MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE bag_item (id INT, bag_id INT, item_id INT);
INSERT INTO bag_item VALUES
(1,		1,	1),
(2,		1,	2),
(3,		2,	1),
(4,		2,	2),
(5,		2,	3),
(6,		2,	4),
(7,		3,	1),
(8,		3,	4),
(9,		4,	1),
(10,	4,	2),
(11,	4,	3);
SELECT * FROM bag_item;

Records: 11  Duplicates: 0  Warnings: 0




id
bag_id
item_id


1
1
1

2
1
2

3
2
1

4
2
2

5
2
3

6
2
4

7
3
1

8
3
4

9
4
1

10
4
2

11
4
3


SET @base_bags = '1,4';
-- detailed 
WITH 
minimal_superset AS (
  SELECT DISTINCT item_id
  FROM bag_item
  WHERE FIND_IN_SET(bag_id, @base_bags)
  ),
bags_list AS (
  SELECT DISTINCT bag_id
  FROM bag_item
  )
SELECT bags_list.*, minimal_superset.*, bag_item.*
FROM minimal_superset
CROSS JOIN bags_list
LEFT JOIN bag_item USING (bag_id, item_id)
ORDER BY bags_list.bag_id, minimal_superset.item_id


bag_id
item_id
id
bag_id
item_id


1
1
1
1
1

1
2
2
1
2

1
3
null
null
null

2
1
3
2
1

2
2
4
2
2

2
3
5
2
3

3
1
7
3
1

3
2
null
null
null

3
3
null
null
null

4
1
9
4
1

4
2
10
4
2

4
3
11
4
3


-- final
WITH 
minimal_superset AS (
  SELECT DISTINCT item_id
  FROM bag_item
  WHERE FIND_IN_SET(bag_id, @base_bags)
  ),
bags_list AS (
  SELECT DISTINCT bag_id
  FROM bag_item
  )
SELECT @base_bags base_bags_list, bag_id superset_bag_id
FROM minimal_superset
CROSS JOIN bags_list
LEFT JOIN bag_item USING (bag_id, item_id)
GROUP BY bag_id
HAVING NOT SUM(bag_item.item_id IS NULL)


base_bags_list
superset_bag_id


1,4
2

1,4
4

bag_id	item_id	id	bag_id	item_id
1	1	1	1	1
1	2	2	1	2
1	3	null	null	null
2	1	3	2	1
2	2	4	2	2
2	3	5	2	3
3	1	7	3	1
3	2	null	null	null
3	3	null	null	null
4	1	9	4	1
4	2	10	4	2
4	3	11	4	3