i dealing tables (for purposes of displaying here) following:
a
a_id | clob_col 1 | value 2 | value 3 | null 4 | value 5 | null 6 | value 7 | value 8 | null 9 | value 10 | value b
b_id |a_id | c_id 10 | 1 | 20 11 | 2 | 20 12 | 6 | 21 13 | 7 | 22 14 | 8 | 22 15 | 9 | 23 c
c_id 20 21 22 23 24 25 (all tables have more columns)
i wish write query return values 3 tables exclude records values match in table c (not in). problem return values table do not link table b when using not in clause on table c.
example:
select a.a_id, a.clob_col, b.b_id, c.c_id a left join b b on a.a_id=b.a_id left join c c on b.c_id=c.c_id a.aid >= 2 , a.aid <= 7 , c.c_id not in (22, 23, 24) the last line - c.c_id not in (22, 23, 24) - leave following records in b table: b_bid = 10,11 or 12
in turn, these link following records in table a: a.id = 1,2 , 6.
the first clause - a.aid >= 2 , a.aid <= 7 - further restricts our final result a.id = 2 , 6. final result like:
a_id |clob_col |b_id |c_id 2 |value |11 |20 6 |value |12 |21 but want return table records don't link table b - a.id 3, 4 , 5 expect results be
a_id |clob_col |b_id |c_id 2 |value |11 |20 3 |null |null |null 4 |value |null |null 5 |null |null |null 6 |value |12 |21 note: reason included clob value suggested selecting records , performing minus operation on records c.c_id in (22, 23, 24).
this seemed reasonable oracle not allow minus operation clob columns involved.
i think you're after:
with (select 1 a_id, 'val1' clob_col dual union select 2 a_id, 'val2' clob_col dual union select 3 a_id, null clob_col dual union select 4 a_id, 'val4' clob_col dual union select 5 a_id, null clob_col dual union select 6 a_id, 'val6' clob_col dual union select 7 a_id, 'val7' clob_col dual union select 8 a_id, null clob_col dual union select 9 a_id, 'val9' clob_col dual union select 10 a_id, 'val10' clob_col dual), b (select 10 b_id, 1 a_id, 20 c_id dual union select 11 b_id, 2 a_id, 20 c_id dual union select 12 b_id, 6 a_id, 21 c_id dual union select 13 b_id, 7 a_id, 22 c_id dual union select 14 b_id, 8 a_id, 22 c_id dual union select 15 b_id, 9 a_id, 23 c_id dual), c (select 20 c_id dual union select 21 c_id dual union select 22 c_id dual union select 23 c_id dual union select 24 c_id dual union select 25 c_id dual) select a.a_id, a.clob_col, b.b_id, c.c_id left outer join b on (a.a_id = b.a_id) left outer join c on (b.c_id = c.c_id) a.a_id between 2 , 7 , (c.c_id not in (22, 23, 24) or c.c_id null) order a.a_id; a_id clob_col b_id c_id ---------- -------- ---------- ---------- 2 val2 11 20 3 4 val4 5 6 val6 12 21 , if c_id 27 a_id = 6 in b table: a_id clob_col b_id c_id ---------- -------- ---------- ---------- 2 val2 11 20 3 4 val4 5 6 val6 12 you have take account of fact c_id null, not being in set of values being excluded.
eta: ponder stibbons' suggestion in comments, if didn't want row displayed a.a_id = b.a_id matches there isn't match on b.c_id = c.c_id, changing or c.c_id null or b.c_id null removes row:
with (select 1 a_id, 'val1' clob_col dual union select 2 a_id, 'val2' clob_col dual union select 3 a_id, null clob_col dual union select 4 a_id, 'val4' clob_col dual union select 5 a_id, null clob_col dual union select 6 a_id, 'val6' clob_col dual union select 7 a_id, 'val7' clob_col dual union select 8 a_id, null clob_col dual union select 9 a_id, 'val9' clob_col dual union select 10 a_id, 'val10' clob_col dual), b (select 10 b_id, 1 a_id, 20 c_id dual union select 11 b_id, 2 a_id, 20 c_id dual union select 12 b_id, 6 a_id, 27 c_id dual union select 13 b_id, 7 a_id, 22 c_id dual union select 14 b_id, 8 a_id, 22 c_id dual union select 15 b_id, 9 a_id, 23 c_id dual), c (select 20 c_id dual union select 21 c_id dual union select 22 c_id dual union select 23 c_id dual union select 24 c_id dual union select 25 c_id dual) select a.a_id, a.clob_col, b.b_id, c.c_id left outer join b on (a.a_id = b.a_id) left outer join c on (b.c_id = c.c_id) a.a_id between 2 , 7 , (c.c_id not in (22, 23, 24) or b.c_id null) order a.a_id; 
Comments
Post a Comment