All geek questions in one place

Oracle sql query

I am dealing with tables that (for display purposes here) look like this:

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 want to write a query that will return values ​​from all three tables, but exclude records where certain values ​​correspond to values ​​in table C (NOT IN). My problem is returning values ​​from table A that do not reference table B when using the NOT IN clause in 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 entries in table B: b_BID = 10.11 or 12

In turn, they refer to the following entries in table A: a.ID = 1,2 and 6.

- a.AID >= 2 AND a.AID <= 7 - a.ID = 2 6. :

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

A, B - a.ID 3, 4 5 ,

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

. , Clob, - , - , MINUS , c.C_ID IN (22, 23, 24).

, Oracle MINUS, Clob.

+4
3

, , :

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

, c_id , .

ETA: Ponder Stibbons , , , a.a_id = b.a_id, b.c_id = c.c_id , or c.c_id is null or b.c_id is null :

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;
+1

, "on" . :

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)

, .

+3

Include what you are joining, and you can also use BETWEENWHERE for the first clause.

Also I would use INNER JOINS and not LEFT according to 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)
+2
source

Source: https://habr.com/ru/post/1598354/

More articles:


All Articles