sql join, left join . records not shown if there's criteria for right table

Table a fields:

id_a

Table b fields:

id_b
id_b2
filed_b
filed_b2

Table c fields:

id_c
filed_c

table a data:

id_a
----
1 
2 

table b data:

id_b  id_b2  filed_b
----  -----  -------
1     1      1  
2     2      100 

table c data:

id_c  filed_c
----  ---------
1     adfa11111
2     dfdf22222

join

a join b on id_a=id_b
b join c on id_b2=id_c

The goal is to get all table a data and associated filed_c data. Criterion is: if filed_b=100, list filed_c. otherwise leave filed_c null.

Problem: used left join, if no criteria on the right table, it's fine. But once there's a crit开发者_JAVA百科eria on right table, the records not exist in right table won't show up.

The trick to get a where condition to work with a left outer join is to put the criteria in the join clause. If you checked against b.filed_b in the where clause you exclude the rows where the values is null which it is when there is no match in table b.

Something like this in your case.

select *
from a
  left outer join b
    on a.id_a = b.id_b and
       b.filed_b = 100
  left outer join c
    on b.id_b2 = c.id_c