All X meeting multi-row conditions

from the Artful Common Queries page

Given this table ... 
drop table if exists mpu;
create table mpu( 
  id int, id_marketplace int, 
  label int, property int
);
insert into mpu values
  (1,10,3,0),(2,10,6,35),(4,10,10,22),
  (5,10,9,0 ),(6,11,3,0),(7,11,6,5),
  (8,11,7,7),(9,11,7,10),(10,11,10,21),
  (11,12,3,0),(12,12,6,5),(13,12,7,8),
  (14,12,7,9),(15,12,10,21),(16,13,3,0),
  (17,13,6,35),(18,13,7,7),(19,13,7,8),
  (20,13,10,20);
select * from mpu;
+------+----------------+-------+----------+
| id   | id_marketplace | label | property |
+------+----------------+-------+----------+
|    1 |             10 |     3 |        0 |
|    2 |             10 |     6 |       35 |
|    4 |             10 |    10 |       22 |
|    5 |             10 |     9 |        0 |
|    6 |             11 |     3 |        0 |
|    7 |             11 |     6 |        5 |
|    8 |             11 |     7 |        7 |
|    9 |             11 |     7 |       10 |
|   10 |             11 |    10 |       21 |
|   11 |             12 |     3 |        0 |
|   12 |             12 |     6 |        5 |
|   13 |             12 |     7 |        8 |
|   14 |             12 |     7 |        9 |
|   15 |             12 |    10 |       21 |
|   16 |             13 |     3 |        0 |
|   17 |             13 |     6 |       35 |
|   18 |             13 |     7 |        7 |
|   19 |             13 |     7 |        8 |
|   20 |             13 |    10 |       20 |
+------+----------------+-------+----------+
... you need the values where (label=6 AND property=5) OR ((label=6 AND property=35) AND (label=7 AND property=7)). The query for the first condition is dead simple ... 
SELECT id_marketplace 
FROM mpu
WHERE label=6 AND property=5;
+----------------+
| id_marketplace |
+----------------+
|             11 |
|             12 |
+----------------+
Obviously, the second condition can be true of no single row, so you can't just write it into a WHERE clause---it's a multi-row condition. The query for it is either ... 
SELECT a.id_marketplace 
FROM mpu a
JOIN mpu b 
  ON a.id_marketplace=b.id_marketplace 
 AND a.label=6 AND a.property=35 
 AND b.label=7 AND b.property=7;
or ... 
SELECT id_marketplace
FROM mpu
WHERE (label=6 
  AND property=35) 
  OR (label=7 AND property=7)
GROUP BY id_marketplace
HAVING COUNT(*) >= 2;
You need id_marketplace values returned by either of those queries. That's a UNION: 
SELECT id_marketplace 
FROM mpu
WHERE label=6 AND property=5
UNION
SELECT a.id_marketplace 
FROM mpu a
JOIN mpu b ON a.id_marketplace=b.id_marketplace 
          AND a.label=6 
          AND a.property=35 
          AND b.label=7 
          AND b.property=7;
+----------------+
| id_marketplace |
+----------------+
|             11 |
|             12 |
|             13 |
+----------------+

Last updated 27 Jan 2025