Q:

IN and NOT IN clause do not apply

So I have this query:
SELECT m.id, (
  SELECT GROUP_CONCAT(
    DISTINCT tag_id
    ORDER BY tag_id
    SEPARATOR '-'
  ) FROM tagging
  WHERE mng_id = m.id
  ORDER BY tag_id DESC
) as tags 
FROM product m 
    INNER JOIN tagging tg ON (m.id = tg.mng_id)
WHERE 1
  AND tg.tag_id IN (34,20) AND tg.tag_id NOT IN (42)
  AND (nme LIKE 'tomo%' OR alt_nme LIKE 'tomo%')
GROUP BY m.id

This query should return records with tag # 34, 20 and do not have tag # 42, the record's name must start with 'tomo'.
But for some reason, it doesn't remove product with tag #42 from the results. Can any one help identify the issue with this query?

A:

If you want records with tags 34 and 20, but not 42, try something like this:
SELECT p.id
FROM product p INNER JOIN
     tagging tg
     ON p.id = tg.mng_id
GROUP BY p.id
HAVING SUM(tg.tag_id = 34) > 0 AND
       SUM(tg.tag_id = 20) > 0 AND
       SUM(tg.tag_id = 42) = 0;

If you want the resulting tags, just add group_concat(tg.tag_id).
This seems simpler than your approach.

