Oracle sql query
I think you forgot to use "on" clause for join. You can try this :
SELECT a.A_ID, a.Clob_Col, b.B_ID, c.C_IDfrom A aLEFT JOIN B b on a.A_ID=b.A_IDLEFT JOIN C c on b.C_ID=c.C_IDWHERE a.A_ID between 2 and 7AND c.C_ID NOT IN (22, 23, 24)Hope it will work.
Include what your joining on and also you can use BETWEEN for the first WHERE clause.
Also i would use INNER JOINS rather then LEFT as per your data.
SELECT a.A_ID, a.Clob_Col, b.B_ID, c.C_IDFROM A aINNER JOIN B b ON a.A_ID = b.B_IDINNER JOIN C c ON b.C_ID = c.C_IDWHERE a.AID BETWEEN 2 AND 7AND c.C_ID NOT IN (22, 23, 24)
I think this does what you're after:
with a as (select 1 a_id, 'val1' clob_col from dual union all select 2 a_id, 'val2' clob_col from dual union all select 3 a_id, null clob_col from dual union all select 4 a_id, 'val4' clob_col from dual union all select 5 a_id, null clob_col from dual union all select 6 a_id, 'val6' clob_col from dual union all select 7 a_id, 'val7' clob_col from dual union all select 8 a_id, null clob_col from dual union all select 9 a_id, 'val9' clob_col from dual union all select 10 a_id, 'val10' clob_col from dual), b as (select 10 b_id, 1 a_id, 20 c_id from dual union all select 11 b_id, 2 a_id, 20 c_id from dual union all select 12 b_id, 6 a_id, 21 c_id from dual union all select 13 b_id, 7 a_id, 22 c_id from dual union all select 14 b_id, 8 a_id, 22 c_id from dual union all select 15 b_id, 9 a_id, 23 c_id from dual), c as (select 20 c_id from dual union all select 21 c_id from dual union all select 22 c_id from dual union all select 23 c_id from dual union all select 24 c_id from dual union all select 25 c_id from dual)select a.a_id, a.clob_col, b.b_id, c.c_idfrom a left outer join b on (a.a_id = b.a_id) left outer join c on (b.c_id = c.c_id)where a.a_id between 2 and 7and (c.c_id not in (22, 23, 24) or c.c_id is null)order by a.a_id; A_ID CLOB_COL B_ID C_ID---------- -------- ---------- ---------- 2 val2 11 20 3 4 val4 5 6 val6 12 21and if c_id is 27 for a_id = 6 in the b table: A_ID CLOB_COL B_ID C_ID---------- -------- ---------- ---------- 2 val2 11 20 3 4 val4 5 6 val6 12 You have to take account of the fact that c_id could be null, as well as not being in the set of values being excluded.
ETA: Thanks to Ponder Stibbons' suggestion in the comments, if you didn't want the row to be displayed where a.a_id = b.a_id matches but there isn't a match on b.c_id = c.c_id, then changing the or c.c_id is null to or b.c_id is null removes that row:
with a as (select 1 a_id, 'val1' clob_col from dual union all select 2 a_id, 'val2' clob_col from dual union all select 3 a_id, null clob_col from dual union all select 4 a_id, 'val4' clob_col from dual union all select 5 a_id, null clob_col from dual union all select 6 a_id, 'val6' clob_col from dual union all select 7 a_id, 'val7' clob_col from dual union all select 8 a_id, null clob_col from dual union all select 9 a_id, 'val9' clob_col from dual union all select 10 a_id, 'val10' clob_col from dual), b as (select 10 b_id, 1 a_id, 20 c_id from dual union all select 11 b_id, 2 a_id, 20 c_id from dual union all select 12 b_id, 6 a_id, 27 c_id from dual union all select 13 b_id, 7 a_id, 22 c_id from dual union all select 14 b_id, 8 a_id, 22 c_id from dual union all select 15 b_id, 9 a_id, 23 c_id from dual), c as (select 20 c_id from dual union all select 21 c_id from dual union all select 22 c_id from dual union all select 23 c_id from dual union all select 24 c_id from dual union all select 25 c_id from dual)select a.a_id, a.clob_col, b.b_id, c.c_idfrom a left outer join b on (a.a_id = b.a_id) left outer join c on (b.c_id = c.c_id)where a.a_id between 2 and 7and (c.c_id not in (22, 23, 24) or b.c_id is null)order by a.a_id;