Oracle sql query

Question

I am dealing with tables which (for the purposes of displaying here) look like the 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 to write a query which will return values from all three tables but exclude records where certain values match those in Table C (NOT IN). My problem is to return the values from Table A which do not link to Table B when using a NOT IN clause on Table C.

Example:

SELECT a.A_ID, a.Clob_Col, b.B_ID, c.C_ID
from A a
LEFT JOIN B b on a.A_ID=b.A_ID
LEFT JOIN C c on b.C_ID=c.C_ID
WHERE a.AID >= 2
AND a.AID <= 7
AND c.C_ID NOT IN (22, 23, 24)

The last line - c.C_ID NOT IN (22, 23, 24) - will leave us with the following records in the B table: b_BID = 10,11 or 12

In turn, these link to the following records in Table A: a.ID = 1,2 and 6.

The first clause - a.AID >= 2 AND a.AID <= 7 - further restricts our final result to a.ID = 2 and 6. The final result will look like:

A_ID |Clob_Col |B_ID |C_ID 
2 |value |11 |20 
6 |value |12 |21 

But I also want to return the A table records which don't link to Table B - a.ID 3, 4 and 5 So I expect my results to 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: The reason I included the Clob value is that someone suggested selecting all records and then performing a MINUS operation on the records where c.C_ID IN (22, 23, 24).

This seemed reasonable however Oracle does not allow MINUS operation where Clob columns are involved.

• michael667: Just a glimpse, but don't you need some ON clause after the JOINs?
• Kritner: a fiddle of this data would be most useful
• Boneist: Regarding joining the c table - if the c_id value for a_id = 6 in the b table was a value not in the c_id table, what would you want the final output to display? Would the a_id = 6 row be displayed or not? (in short, I'm asking if the c table should be inner or outer joined to the b table, and if outer joined, whether you want nulls to be counted as not being in (22,23,24) or not)
• p45: Thank you every one for noticing the missing ON s. This was an omission which has been rectified. It doesn't address the question though.

Answer 1

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_ID
from A a
LEFT JOIN B b on a.A_ID=b.A_ID
LEFT JOIN C c on b.C_ID=c.C_ID
WHERE a.A_ID between 2 and 7
AND c.C_ID NOT IN (22, 23, 24)

Hope it will work.

• p45: Once again thank you every one for noticing the missing ON s. This was an omission which has been rectified. It doesn't address the question though.

Answer 2

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_ID
FROM A a
INNER JOIN B b ON a.A_ID = b.B_ID
INNER JOIN C c ON b.C_ID = c.C_ID
WHERE a.AID BETWEEN 2 AND 7
AND c.C_ID NOT IN (22, 23, 24)

• p45: Not addressing question. Still provides same result

Answer 3

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_id
from 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 7
and (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 21
and 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_id
from 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 7
and (c.c_id not in (22, 23, 24) or b.c_id is null)
order by a.a_id;

• Ponder Stibbons: I think that this is best answer, the only thing is: I also want to return the A table records which don't link to Table B. The difference is small - b.b_id is null instead of c.c_id is null - it's visible if we insert into B (16, 4, 55).
• Boneist: @PonderStibbons I'm not sure I get you - (16, 4, 55) does mean that A is linked to B based on a_id = 4? My query gives an output of (4, val4, 16, null) - what were you thinking it should return?
• Boneist: @PonderStibbons hmm, I see what you mean - it's whether or not the OP wants the row returned if there's no match on c.c_id = b.c_id when a.b_id = b.b_id. I was trying to get that clarified in my comment on the question, although I'm not sure if I made myself clear enough up there!
• Boneist: @PonderStibbons updated my answer with your suggestion - thanks. I was scratching my head a bit as to how you'd exclude that row if you didn't want it showing! All credit to you for that *{:-)
• p45: Many thanks for answering the question asked. Works a charm.