如果在连接查询中没有B值，则获取具有A值的项目（重复项）

Question

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

Answer 1

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))