oracle left outer joins not showing right null values

Question

hi, i'm having an issue with creating a query in oracle which doesnt seem to want to join on missing values

the table i have is this:

table myTable(refnum, contid, type)

values are:
1, 10, 90000
2, 20, 90000
3, 30, 90000
4, 20, 10000
5, 30, 10000
6, 10, 20000
7, 20, 20000
8, 30, 20000

a break down of the fields i'm after is this:

select a.refnum from myTable a where type = 90000
select b.refnum from myTable b where type = 10000 and contid in (select contid from myTable where type = 90000)
select c.refnum from myTable c where type = 20000 and contid in (select contid from myTable where type = 90000)

the outcome of the query i'm after is this:

a.refnum, b.refnum, c.refnum

i thought this would work:

select a.refnum, b.refnum, c.refnum
from myTable a 
left outer join myTable b on (a.contid = b.contid) 
left outer join myTable c on (a.contid = c.contid) 
where a.id_tp_cd = 90000
and b.id_tp_cd = 10000
and c.id_tp_cd = 20000

so the values should be:

1, null, 6
2, 4, 7
3, 5, 8

but its only returning:

2, 4, 7
3, 5, 8

i thought left joins would show all values in the left and create a null for the right.

help :(

Answer 1

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 = 10000
and c.id_tp_cd = 20000

You 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.refnum
from myTable a 
left outer join myTable b on (a.contid = b.contid and b.id_tp_cd = 90000) 
left outer join myTable c on (a.contid = c.contid and c.id_tp_cd = 20000) 
where a.id_tp_cd = 90000

I haven't tested this but it should work in theory

Answer 2

thank you so much tom :D

really appreciated.

i guessed it was something to do with me restricting the values but i didnt quite know how to write it out