By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 8.0.30 |
create table prod_ch_val (prod_id int, ch_id int, val int);
insert into prod_ch_val values
(1,1,5),
(1,1,6),
(1,2,1),
(2,2,1),
(3,1,5),
(3,1,6),
(3,2,1),
(4,1,5),
(4,1,6),
(4,2,1),
(4,2,2),
(5,2,1);
Records: 12 Duplicates: 0 Warnings: 0
WITH param AS (
SELECT 5 AS prod_id -- ИД продукта которому ищем сопутствующие
)
SELECT p2.prod_id
FROM param, prod_ch_val p1, prod_ch_val p2
WHERE p1.prod_id = param.prod_id
AND p1.ch_id=p2.ch_id
AND p1.val = p2.val
AND p1.prod_id != p2.prod_id
AND (SELECT count(1) FROM prod_ch_val t WHERE t.prod_id=p2.prod_id) = (SELECT count(1) FROM prod_ch_val t WHERE t.prod_id=p1.prod_id)
GROUP BY p2.prod_id
| prod_id |
|---|
| 2 |