mytom mytom - 2 months ago 12
MySQL Question

Mysql: Select rows from a join table where 'in' and 'not in' criteria are used

I have 3 tables like below:

Table media:

+------------------------+
| media_id | media_name |
+------------------------+
| 1 | item1 |
| 2 | item2 |
| 3 | item3 |
+------------------------+


Join Table mediatag:

+--------------------+
| media_id | tag_id |
+--------------------+
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 3 | 1 |
| 3 | 3 |
+--------------------+


Table tag:

+--------------------+
| tag_id | tag_name |
+--------------------+
| 1 | blue |
| 2 | red |
| 3 | white |
| 4 | green |
+--------------------+


I wish retrieve all medias that have 'blue' and 'white' tags but without medias that have 'red' tag.

So in my example, the result must be: item2, item3

I tried this query but obviously the item1 is displayed:

SELECT m.media_id, media_name FROM media AS m
INNER JOIN mediatag AS mag ON m.media_id = mag.media_id
WHERE tag_id = '1' OR tag_id = '3' AND tag_id !='2';


how to do this?

Answer

Group your data and select only those groups having the conditions you mention

SELECT m.media_id, m.media_name 
FROM media AS m 
INNER JOIN mediatag AS mag ON m.media_id = mag.media_id 
GROUP BY m.media_id, m.media_name 
HAVING sum(tag_id in (1,3)) > 0 
   AND sum(tag_id = 2) = 0