Search code examples
mysqlsqljoincountrelational-division

Select only entities with n relations from an n:m relationship

I have an m:n relationship of images and tags in my database, which uses a crosstable to model this. Table imgs contains much more information than just img_id, but that is all that's required to uniquely identify an image.

I want to find every img_id which has both tagA and tagB (and tagC and so on, I'll build this string so it won't really matter whether its two or ten tags).

enter image description here

Now, where I'm stuck is, of course first you'll join imgs with img_tags with tags, add a where clause for the tags;

SELECT * 
FROM imgs 
INNER JOIN img_tags ON imgs.img_id = img_tags.img_id 
INNER JOIN tags     ON img_tags.tag_id = tags.tag_id
WHERE tag = 'tagA' OR tag = 'tagB';

and then you'll get rows with identical imgs information, only differing in tag and tag_id. Now I should be able to count those, targeting only those which appear in the same amount as tags were supplied (Count(*) = n), and then using group by to aggregate them? But I can't quite figure it out. In case it might be of relevance, you might assume the fields in img_tags are both foreign keys referencing the other tables, however that is not the case, they are not linked in any way.

Solution

  • You can use aggregation like this:

    SELECT i.* 
FROM imgs i JOIN
     img_tags it
     ON i.img_id = it.img_id JOIN
     tags t
     ON it.tag_id = t.tag_id
WHERE tag IN ('tagA', 'tagB')
GROUP BY i.img_id
HAVING COUNT(*) = 2;

    Aggregating by i.img_id is safe -- and supported by the SQL standard -- assuming that img_id is the primary key in the table.