By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table Packages (
id int,
name varchar(20)
);
insert into Packages values
(1, 'red'),
(2, 'blue'),
(3, 'yellow');
create table Contents (
packageid int,
item varchar(20),
size varchar(20)
);
insert into Contents values
(1, 'square', 'A'),
(1, 'circle', 'B'),
(1, 'triangle', 'C'),
(2, 'square', 'A'),
(2, 'circle', 'B'),
(3, 'square', 'A');
Records: 3 Duplicates: 0 Warnings: 0
Records: 6 Duplicates: 0 Warnings: 0
select *
from Packages p
inner join Contents c on c.packageid = p.id
| id | name | packageid | item | size |
|---|---|---|---|---|
| 1 | red | 1 | square | A |
| 1 | red | 1 | circle | B |
| 1 | red | 1 | triangle | C |
| 2 | blue | 2 | square | A |
| 2 | blue | 2 | circle | B |
| 3 | yellow | 3 | square | A |
with filter as (
select 'square' item, 'A' size
),
filter_with_count as (
select *, count(*) over() as cnt
from filter
),
cte as (
select p.id, p.name, c.item, c.size,
count(p.id) over(partition by p.id) as cnt
from Packages p
inner join Contents c on c.packageid = p.id
)
select c.id, c.name, c.item, c.size
from cte c
inner join filter_with_count f
on c.item = f.item
and c.size = f.size
and c.cnt = f.cnt
| id | name | item | size |
|---|---|---|---|
| 3 | yellow | square | A |
with filter as (
select 'square' item, 'A' size union all
select 'circle', 'B'
),
filter_with_count as (
select *, count(*) over() as cnt
from filter
),
cte as (
select p.id, p.name, c.item, c.size,
count(p.id) over(partition by p.id) as cnt
from Packages p
inner join Contents c on c.packageid = p.id
)
select c.id, c.name, c.item, c.size
from cte c
inner join filter_with_count f
on c.item = f.item
and c.size = f.size
and c.cnt = f.cnt
| id | name | item | size |
|---|---|---|---|
| 2 | blue | square | A |
| 2 | blue | circle | B |
with filter as (
select 'square' item, 'A' size union all
select 'circle', 'B' union all
select 'triangle', 'C'
),
filter_with_count as (
select *, count(*) over() as cnt
from filter
),
cte as (
select p.id, p.name, c.item, c.size,
count(p.id) over(partition by p.id) as cnt
from Packages p
inner join Contents c on c.packageid = p.id
)
select c.id, c.name, c.item, c.size
from cte c
inner join filter_with_count f
on c.item = f.item
and c.size = f.size
and c.cnt = f.cnt
| id | name | item | size |
|---|---|---|---|
| 1 | red | square | A |
| 1 | red | circle | B |
| 1 | red | triangle | C |