Edit Question - KnowledgeHub

Title Body

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 :(

Tags (comma-separated) Cancel