SQL: left join query

Question

I am having problems forming an sql query.

Table stucture:

 TABLE A:
 id     | data_a 
--------|--------
 1      | data1  
 2      | data2   
 3      | data3   

 TABLE B:
  id     | data_b | a_id 
 --------|--------|--------
  1      | data4  | 1
  2      | data5  | 1
  3      | data6  | 2
  4      | data7  | 3
  5      | data8  | 3

 TABLE C:
  id     | data_c | b_id   | x_id   
 --------|--------|--------|--------
  1      | data9  | 1      | 1
  2      | data10 | 2      | 1
  3      | data11 | 3      | 1
  4      | data12 | 1      | 2
  5      | data13 | 4      | 2

Required output:

 data_a | data_b | data_c 
--------|--------|--------
 data1  | data4  | data12
 data1  | data5  | 
 data2  | data6  | 
 data3  | data7  | data13
 data3  | data8  |

Current sql:

SELECT data_a, data_b, data_c
FROM a 
LEFT JOIN b ON a.id = b.a_id 
LEFT JOIN c ON b.id = c.b_id
WHERE c.x_id = 2 OR c.x_id = null;

SQL fiddle

I generally upvote qustions with sql fiddles and downvote questions wth table a, table b, table c. why do you go out of your way to make things difficult? — e4c5
– e4c5, Commented Jan 6, 2017 at 6:33
@e4c5 I prefer fiddles to, didn't want to get told off for a required part of the question being hosted elsewhere. — meeeee
– meeeee, Commented Jan 6, 2017 at 6:36
I just explained why I didn't downvote your question for all your a,b,c stuff — e4c5
– e4c5, Commented Jan 6, 2017 at 6:37

wonderbell · Accepted Answer · 2017-01-06 06:41:22Z

5

Put the x_id = 2 in left join condition.

SELECT
  data_a, 
  data_b, 
  data_c

FROM
  a LEFT JOIN b ON a.id = b.a_id
    LEFT JOIN c ON b.id = c.b_id and c.x_id=2;

answered Jan 6, 2017 at 6:41

wonderbell

1,1249 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

A Biswas · Accepted Answer · 2017-01-06 06:39:04Z

1

Try this : -

SELECT
  data_a, 
  data_b, 
  data_c

FROM
  (Select data_a,data_b,b.id from a LEFT JOIN b ON a.id = b.a_id) as k
    LEFT JOIN (Select * from c WHERE
  c.x_id = 2 OR c.x_id = null) as j ON k.id = j.b_id

for exact match just add this :-

order by data_a,data_b

answered Jan 6, 2017 at 6:39

A Biswas

4316 silver badges12 bronze badges

1 Comment

meeeee Over a year ago

thanks for your help. This does work but wonderbell's answer does not require a nested select statement

Collectives™ on Stack Overflow

SQL: left join query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related