Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
- 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)
- a.refnum, b.refnum, c.refnum
- 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
- 1, null, 6
- 2, 4, 7
- 3, 5, 8
- 2, 4, 7
- 3, 5, 8
- and b.id_tp_cd = 10000
- and c.id_tp_cd = 20000
- 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 = 10000)
- left outer join myTable c on (a.contid = c.contid and c.id_tp_cd = 20000)
- where a.id_tp_cd = 90000
- select a.refnum, b.refnum, c.refnum
- from myTable a, mytable b, mytable c
- where a.contid=b.contid(+)
- and a.contid=c.contid(+)
- and a.type = 90000
- and b.type(+) = 10000
- and c.type(+) = 20000;
- REFNUM REFNUM REFNUM
- ---------- ---------- ----------
- 1 6
- 2 4 7
- 3 5 8
Add Comment
Please, Sign In to add comment