SQL Server Performance Forum – Threads Archive
SQL Statemetn HELP
HiTable A has an ID field
Table B has ID field and AccountsID I need to form a query that will match any record in table A with multiple records in table B Table A may or may not match any records in table B
Table B, when there is a match, will have one OR multiple matching records, i.e.: ID AccID
Table A ID 1234
Table B ID 1234 789
Table B ID 1234 567
Table B ID 1234 765 Any help would be greatly appreciated.
select *
from TableA a inner join TableB b
on a.ID = b.ID
KH
Yes, I’ve tried that. The problem is that the left side of the query will return one record, but the right side will have as many as 6 corresponding records. I need to show the one record from the left side AND display each of the records from the right side.
You have to define which of the record on the RIGHT side that you want ? Post some sample data for both tables and the result you want.
KH
Select a.accountID, a.userid, b.accountid, b.userid, b.account_type, b.account_open
From load a INNER JOIN account b
ON a.userid = b.userid
Where 23424 555 23424 555 17 04/04/2005 In the above example, there is one account in table A, but there are 4 accounts in table B, but because there is only one account in table A, the other 3 accounts in table B are not displayed. In the rare occasion that there are two accounts in table A, then two of the corresponding table B accounts are displayed with the 2 accounts from table A. The idea here is that there is normally a one (table A) to many (table B) relationship between table A and Table B. Occasionally, there will be two records in table A with 6 corresponding records in table B. However, I can not get all the records in table B to match with the 1 or 2 records that match from Table A.
Still don’t quite get what you want. It will definate help if you could provide some sample data and the result that you want. Also look up Books OnLine on JOINS like INNER JOIN, LEFT OUTER JOIN , FULL OUTER JOIN etc. Sounds like you might need a LEFT JOIN
KH
I can’t get coherient enough results to post anything meaningful. Basically, in table A there is a record with a userid field. That userid field has to be joined with many records in the B table, using the userid as the joining field. I have tried a left join with the same results,i.e., one record from the left side matching only one record from the right.
Can you post your table strcutre, sample data and result that you want in the below format ?
Sometimes, it is much clearer to explain things with some data rather than descriptive words. create table load
(
accountid int,
userid int
)
create table account
(
accountid int,
userid int,
account_type int,
account_open datetime
)
insert into load
select 23424, 555 union all
select 23425, 666 insert into account
select 23424, 555, 17, ‘2005/04/04’ union all
select 23424, 777, 18, ‘2005/04/04’ union all
select 23425, 666, 19, ‘2005/05/05’ — The result that i want is :
<tell us how do you want the result>
KH
Try with all joins i.e.
left join,right join or in the end full outer join.
You will get all the rows by any of the above join method.
The thing is when you say left join it depends on the table which you have kept on left side.
So try with all joins if you are not sure.
FROM A INNER JOIN B ON A.userid = B.userid
… will show all rows from A and B where there is a match on userid. FROM A LEFT JOIN B ON A.userid = B.userid
… will show all rows from A, and any rows from B where there’s a match. If there is no match in B, the columns from A will be shown, with NULLs on all columns from B. FROM A RIGHT JOIN B ON A.userid = B.userid
… will show all rows from B, and any rows from A where there’s a match. If there is no match in A, the columns from B will be shown, with NULLs on all columns from A. FROM A FULL JOIN B ON A.userid = B.userid
… will show all rows from A and B, even if there is no match on userid. If there’s no match in A, the columns from B will be shown, with NULLs on all columns from A. If there’s no match in B, the columns from A will be shown, with NULLs on all columns from B. In all cases, if there are multiple rows on either side that have a matching userid value, then multiple rows will be returned. For the outer joins, I use LEFT JOINs almost exclusively. In Access, which has a slightly different JOIN syntax where you must use brackets when joining more than two tables, sometimes I do use a RIGHT JOIN – but not if I can avoid it. Ah yes, as long as you’re not building queries in Enterprise Manager, and you’re not accessing SQL Server db’s before version 6.5, you can drop the OUTER keyword from LEFT and RIGHT joins. AFAIK, OUTER has no function in the current SQL standard. (The point with Enterprise Manager being that it always adds the OUTER keyword, and re-arranges your lay-out.)
]]>