4

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.

Improve this question
4
  • Just a glimpse, but don't you need some ON clause after the JOINs? Commented Jul 17, 2015 at 10:18
  • 1
    a fiddle of this data would be most useful Commented Jul 17, 2015 at 10:19
  • 1
    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) Commented Jul 17, 2015 at 10:37
  • 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. Commented Jul 17, 2015 at 14:18

3 Answers 3

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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.
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)
Improve this answer

1 Comment

Not addressing question. Still provides same result
1

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;
Improve this answer

5 Comments

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).
@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?
@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!
@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 *{:-)
Many thanks for answering the question asked. Works a charm.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.