Get records using left outer join
I have two tables as given below
Table A Table B Table C ============= ============== ========= Id Name Id AId CId Id Name 1 A 1 1 1 1 x 2 B 2 1 1 2 y 3 C 3 2 1 3 z 4 D 4 2 3 4 w 5 E 5 3 2 5 v
Now I want all the records of Table A with matching Id column CId from Table B where CId = 1.
So the output should be like below :
Id Name CId 1 A 1 2 B 1 3 C 1 4 D Null 5 E Null
Can anyone help me please?
This does what you want:
SELECT A.Id, A.Name, CASE B.CId WHEN 1 THEN 1 ELSE NULL END AS CId FROM A LEFT JOIN B ON A.Id = B.Id
This is not about LEFT JOINing. You could as well do it with an INNER JOIN. When you don't want the 3 and 2 of column CId to appear you would still have to filter with WHERE and therefore the rows with Id 4 and 5 would not appear, which is not what you want.
Given this test data:
create table A (Id int, Name varchar(5)); insert into A values (1, 'A'), (2, 'B'), (3, 'C'), (4, 'D'), (5, 'E'); create table B (Id int, AId int, CId int); insert into B values (1,1,1), (2,1,1), (3,2,1), (4,2,3), (5,3,2);
my query does not give a cartesian product. Read and try before downvoting. Anyway, it was not clear to me what you want to achieve, now I've joined on AId column and with this query:
SELECT DISTINCT A.Id, A.Name , CASE WHEN B.CId > 1 THEN 1 WHEN B.CId = 1 THEN 1 ELSE NULL END AS CId FROM A LEFT JOIN B ON A.Id = B.AId
and it also gives the right output, like the first before. If this is still not what you want, your test data is wrong or I absolutely don't get it.
Try something like this:
SELECT TableA.Id, TableA.Name, TableB.CId FROM TableA LEFT OUTER JOIN TableB ON TableA.Id = TableB.CId WHERE TableB.CId = 1
Hope this helps.
Edit: The output you desired, can be achieved if you match TableA's ID column with TableB's ID column, NOT TableB's CId column. Try below which I tested in my pc and gives thee similar output you needed.
select TableA.Id, TableA.Name, TableB.CId from TableA left outer join TableB on TableA.Id = TableB.Id and TableB.CId in ( select TableB.CId from TableB left outer join TableC on TableB.CId = TableC.Id WHERE TableB.CId = 1 ) group by TableA.Id, TableA.Name, TableB.CId
Please inform if I guess it right. Check the column names.