Mohamed Ben Salah Mohamed Ben Salah - 21 days ago 5
MySQL Question

Select on join table with exact number of itmes

I have two tables


  • tracks

  • tags



One track have many tags

I want to have list of tracks that have both of two tags example tag_id 1 and tag_id 2

SELECT * FROM tracks
LEFT JOIN tags ON tracks.tag_id = tags.id
WHERE tags.id in (1,2)
GROUP BY track.id
HAVING count(tags.id) = 2


The problem if a tracks have tag 1 and 3 it will be listed.

any help please?

Answer

Add distinct to count

SELECT track.id FROM tracks 
LEFT JOIN tags ON tracks.tag_id = tags.id
WHERE tags.id in (1,2)
GROUP BY track.id
HAVING count(Distinct tags.id) = 2

You can change the LEFT JOIN to INNER JOIN since it is converted implicitly based on your Where clause