sorry if the title is vague .
i have these table :
image : id , title
image_tags : image_id , tag
image_categories : image_id , category_id
in the image page , i want to get similar images by tag , from the same category but i want to exclude some categories
so here is my query :(lets say my curernt image has category : 1 , tag : a,b and i want to ignore category : 6,7)
SELECT image_tags.image_id
FROM image_tags
JOIN image_categories
ON image_categories.image_id = image_tags.image_id
WHERE image_tags.tag IN ( 'a', 'b' ) && image_categories.category_id IN ( 1 )
&&
image_categories.category_id NOT IN ( 6, 7 )
that works but if i have
image_tags ( image_id , tag ) :
`128` , `a`
--------------------------------------------
image_categories (image_id , category_id ) :
`128` , `1`
`128` , `6`
i'll get the image with id = 128 on account of the first row in the image_categories
i have to somehow check and ignor image 128 becuz if second row ( category_i : 6 )
hopefully my question is not very confusing
Group on image_id and test the categories using a suitable aggregate function within the HAVING clause:
SELECT image_id
FROM image_tags JOIN image_categories USING (image_id)
WHERE image_tags.tag IN ('a','b')
GROUP BY image_id
HAVING SUM(image_categories.category_id IN (1))
AND NOT SUM(image_categories.category_id IN (6,7))