code_junkie

oracle left outer joins not showing right null values

Nov 12th, 2011
87
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.34 KB | None | 0 0
  1. table myTable(refnum, contid, type)
  2.  
  3. values are:
  4. 1, 10, 90000
  5. 2, 20, 90000
  6. 3, 30, 90000
  7. 4, 20, 10000
  8. 5, 30, 10000
  9. 6, 10, 20000
  10. 7, 20, 20000
  11. 8, 30, 20000
  12.  
  13. select a.refnum from myTable a where type = 90000
  14. select b.refnum from myTable b where type = 10000 and contid in (select contid from myTable where type = 90000)
  15. select c.refnum from myTable c where type = 20000 and contid in (select contid from myTable where type = 90000)
  16.  
  17. a.refnum, b.refnum, c.refnum
  18.  
  19. select a.refnum, b.refnum, c.refnum
  20. from myTable a
  21. left outer join myTable b on (a.contid = b.contid)
  22. left outer join myTable c on (a.contid = c.contid)
  23. where a.id_tp_cd = 90000
  24. and b.id_tp_cd = 10000
  25. and c.id_tp_cd = 20000
  26.  
  27. 1, null, 6
  28. 2, 4, 7
  29. 3, 5, 8
  30.  
  31. 2, 4, 7
  32. 3, 5, 8
  33.  
  34. and b.id_tp_cd = 10000
  35. and c.id_tp_cd = 20000
  36.  
  37. select a.refnum, b.refnum, c.refnum
  38. from myTable a
  39. left outer join myTable b on (a.contid = b.contid and b.id_tp_cd = 10000)
  40. left outer join myTable c on (a.contid = c.contid and c.id_tp_cd = 20000)
  41. where a.id_tp_cd = 90000
  42.  
  43. select a.refnum, b.refnum, c.refnum
  44. from myTable a, mytable b, mytable c
  45. where a.contid=b.contid(+)
  46. and a.contid=c.contid(+)
  47. and a.type = 90000
  48. and b.type(+) = 10000
  49. and c.type(+) = 20000;
  50.  
  51.  
  52. REFNUM REFNUM REFNUM
  53. ---------- ---------- ----------
  54. 1 6
  55. 2 4 7
  56. 3 5 8
Add Comment
Please, Sign In to add comment