Simple SQL Query for many to many table

3179 views mysql
6

I have a table with the following data in it (see image in link below)

my database table

I want to simply only return rows where a location id has exactly tag_ids 6 and 2.

So in the example in the image, only the first two rows should be returned (with location_id 1) because even though location_id 2 also has tag id 6, it does not have tag_id 2 so I dont want to show location_id 2.

Sorry if that sounds confusing but in my head it should be a straight forward SQL query.

answered question

What if it has 6, 2, and 3?

1 Answer

6

Here is one option:

SELECT location_id
FROM yourTable
WHERE tag_id IN (2, 6)
GROUP BY location_id
HAVING COUNT(*) = 2;

posted this

Have an answer?

JD

Please login first before posting an answer.