select_where_match_all

View All Public Scripts Login to Run Script

Script Name select_where_match_all
Visibility Unlisted - anyone with the share link can access
Description select all the rows in table 3 where they have match to all the rows for a given key in table 1, by way of an intersection table.
Area SQL General / SQL Query
Contributor Don Simpson
Created Monday February 18, 2019

Statement 1

create table T1 (OR_ID integer, CD_ID integer)

Table created.

Statement 2

insert into T1 (OR_ID, CD_ID) values (1, 1)

1 row(s) inserted.

Statement 3

insert into T1 (OR_ID, CD_ID) values (1, 2)

1 row(s) inserted.

Statement 4

insert into T1 (OR_ID, CD_ID) values (2, 11)

1 row(s) inserted.

Statement 5

insert into T1 (OR_ID, CD_ID) values (2, 12)

1 row(s) inserted.

Statement 6
```
commit
```
```
Statement processed.
```

Statement 7

create table T2 (CD_ID2 integer, R_ID integer)

Table created.

Statement 8

insert into T2 (CD_ID2, R_ID) values (1, 101)

1 row(s) inserted.

Statement 9

insert into T2 (CD_ID2, R_ID) values (2, 102)

1 row(s) inserted.

Statement 10

insert into T2 (CD_ID2, R_ID) values (11, 211)

1 row(s) inserted.

Statement 11

insert into T2 (CD_ID2, R_ID) values (12, 212)

1 row(s) inserted.

Statement 12
```
commit
```
```
Statement processed.
```

Statement 13

create table T3 (L_ID integer, R_ID2 integer)

Table created.

Statement 14

insert into T3 (L_ID, R_ID2) values (1001, 101)

1 row(s) inserted.

Statement 15

insert into T3 (L_ID, R_ID2) values (1001, 102)

1 row(s) inserted.

Statement 16

insert into T3 (L_ID, R_ID2) values (1002, 101)

1 row(s) inserted.

Statement 17

insert into T3 (L_ID, R_ID2) values (1002, 211)

1 row(s) inserted.

Statement 18

insert into T3 (L_ID, R_ID2) values (1003, 211)

1 row(s) inserted.

Statement 19

insert into T3 (L_ID, R_ID2) values (1003, 212)

1 row(s) inserted.

Statement 20

insert into T3 (L_ID, R_ID2) values (1004, 102)

1 row(s) inserted.

Statement 21

insert into T3 (L_ID, R_ID2) values (1004, 212)

1 row(s) inserted.

Statement 22

insert into T3 (L_ID, R_ID2) values (1004, 101)

1 row(s) inserted.

Statement 23

insert into T3 (L_ID, R_ID2) values (1004, 300)

1 row(s) inserted.

Statement 24
```
commit
```
```
Statement processed.
```

Statement 25

with o as ( 
    select T1.OR_ID, T1.CD_ID, T2.R_ID,  
        count(*) over (partition by T1.OR_ID) as CNT   
    from T1  
    join T2  
        on T2.CD_ID2 = T1.CD_ID 
    ),  
x as ( 
    select o.OR_ID, o.CNT, T3.L_ID, count(*) as CNT_T3  
    from o  
    join T3  
        on T3.R_ID2 = o.R_ID  
    group by o.OR_ID, o.CNT, T3.L_ID  
    ) 
select x.OR_ID, x.CNT, x.L_ID, x.CNT_T3  
from x  
    where CNT = CNT_T3  
order by x.OR_ID, x.L_ID


OR_ID CNT L_ID CNT_T3
1 2 1001 2
1 2 1004 2
2 2 1003 2

3 rows selected.

OR_ID	CNT	L_ID	CNT_T3
1	2	1001	2
1	2	1004	2
2	2	1003	2

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database