Formulating TSQL Left Join

I am using SSMS 2008. Should be a simple solution to this. I am trying to pull all records from table A, but only pull in matching records from tables B and C. The problem is that if I left join tables B and C, it returns records where all 3 of these tables intersect instead. Like if I query this combined merge, I get non-NULL values for every row for both B and C.

Here is my pseudocode:

SELECT A.ID, B.ID, C.ID
FROM A
LEFT JOIN B ON B.ID = A.ID
LEFT JOIN C ON C.ID = A.ID

In answer to your questions, I am sorry I forgot the "LEFT", but I just added it above. If table A has 9 rows and B has 2 rows and C has 3 rows, then what I want to see above is where table A intersects B and where A intersects C. So in the scenario just described, assuming that the Table B rows are all different than the Table C rows, then I want to see a total of 5 ro开发者_高级运维ws; 2 from B and 3 from C. Make sense?

---

The ideal way to do this is with a LEFT JOIN. I believe (though it's unclear from your question) that the problem you have is multiple rows per A.ID.

If A.ID matches B.ID AND C.ID then you are getting two rows for this, and would like one consolidated row.

I think maybe you had your JOIN conditions mixed up in your test query. This is working fine for me in testing. Try the below query:

DECLARE @A TABLE (ID INT)
DECLARE @B TABLE (ID INT)
DECLARE @C TABLE (ID INT)

INSERT INTO @A VALUES
(1),
(2),
(3),
(4)

INSERT INTO @B VALUES
(2),
(3)

INSERT INTO @C VALUES
(3),
(4)

SELECT A.ID, B.ID, C.ID
FROM @A A
LEFT JOIN @B B
    ON B.ID = A.ID
LEFT JOIN @C C
    ON C.ID = A.ID

Output is:

ID  ID      ID
1   NULL    NULL
2   2       NULL
3   3       3
4   NULL    4

---

what you want is a join to a union stamtement.

SELECT A.ID, 
case when D.tablename = 'B' then  ID else null end, 
case when D.tablename = 'C' then  ID else null end
FROM A 
JOIN 
(select Id, 'b' as tablename from B
union all
select id, 'c' from c)
D ON D.ID = A.ID 

---

The problem statement is still inconsistent. I think you mean where table A intersects B OR where A intersects C. In that case the proper TSQL is:

    SELECT A.ID
    FROM A
    LEFT OUTER JOIN B ON B.ID = A.ID
    LEFT OUTER JOIN C ON C.ID = A.ID
    WHERE B.ID IS NOT NULL OR C.ID IS NOT NULL 
    GROUP BY A.ID 
    ORDER BY A.ID

No need to report B.ID and C.ID if they equal A.ID. Group By is so you don't get repeated A.ID.

---

SELECT A.ID, B.ID, C.ID
FROM A
LEFT JOIN B ON B.ID = A.ID
LEFT JOIN C ON C.ID = A.ID
WHERE B.ID IS NOT NULL OR C.ID IS NOT NULL

Or, if you need only fields from A, then

SELECT A.ID
FROM A
INNER JOIN (
    SELECT B.ID FROM B
    UNION
    SELECT C.ID FROM C ) AS U ON U.ID = A.ID