oracle left outer joins not showing right null values
You are correct in saying that left joins will return nulls for the right where there is no match, but you are not allowing these nulls to be returned when you add this restriction to your where clause:
and b.id_tp_cd = 10000and c.id_tp_cd = 20000You should be able to put these in the 'on' clause of the join instead, so only relevant rows on the right are returned.
select a.refnum, b.refnum, c.refnumfrom myTable a left outer join myTable b on (a.contid = b.contid and b.id_tp_cd = 10000) left outer join myTable c on (a.contid = c.contid and c.id_tp_cd = 20000) where a.id_tp_cd = 90000
Or using the Oracle syntax instead of ansi
select a.refnum, b.refnum, c.refnumfrom myTable a, mytable b, mytable cwhere a.contid=b.contid(+)and a.contid=c.contid(+)and a.type = 90000and b.type(+) = 10000and c.type(+) = 20000;REFNUM REFNUM REFNUM---------- ---------- ---------- 1 6 2 4 7 3 5 8