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 |
+----------------+


Return to the Artful Common Queries page