jeudi 3 décembre 2015

Select where item contains all tags but does not have any unwanted tags

I have a database set up where media items have many tags, whose relations are linked through a media_tags table. I want to be able to request media items that have certain tags, but that don't have certain other tags.

For example, let's say I wanted all items that had the tags 'vehicle' AND 'object', but not items that had the tags 'truck' OR 'van'. I've figured out how to do the first part already:

SELECT * FROM media
INNER JOIN media_tags ON media_tags.media_id = media.id
INNER JOIN tags ON (tags.id=media_tags.tag_id)
WHERE (tags.name = 'object' OR tags.name = 'vehicle')
GROUP BY media.id
HAVING COUNT(distinct media_tags.tag_id) = 2;

But I can't figure out how to write a query that ignores media items that have either 'truck' or 'van' as tags.

Aucun commentaire:

Enregistrer un commentaire