Need help rectifying IN/NOT IN clause Hi there everyone!

I have built a link/content sharing site. The way it works is that each link can have multiple categories assigned to it. A video of a motorcycle race might have motorcycle, racing, extreme and trending attached to it. The way it gets stored into the database is by a linkcats table in which it stores the link_id and the cat_id. The example above would result in 4 records for that link_id, since it had four categories assigned to it.

The site learns what people like as they browse. As they view, vote on and post content, the site figures out their most and least favorite categories. My intention is to display the results of what the site thinks is their most favorite while cutting out what it thinks is their least favorite. To do that, I'm trying to display links from their 5 most favorite categories while nixing them if any unfave cats are assigned to them.

Here's what I tried:

SELECT shortenified_urls.*
FROM shortenified_urls
LEFT JOIN linkcats ON linkcats.link_id = shortenified_urls.id
WHERE linkcats.cat_id IN (17,1,35,33,50)
AND linkcats.cat_id NOT IN (28)
AND shortenified_urls.is_social = '1'
AND shortenified_urls.active = '1'
GROUP BY shortenified_urls


This didn't work, I think because of the multiple categories. Since the links are being matched more than once due to multiple category matches, even if links matching 28 get passed by, if that same link gets matched on another of it's categories, it will still get included in the results. At least, I think that's what's happening.

Does anyone have an idea on how I might alter the query to achieve the desired result?

Thanks for your time!
Your LEFT JOIN is converted to a INNER join because the WHERE contains conditions about the columns of the right (linkcats) table. Then, since the conditions are contradicting, the query will always return 0 rows:

WHERE linkcats.cat_id IN (17,1,35,33,50)
AND linkcats.cat_id NOT IN (28)


You need a semijoin and an anti-semijoin, which can be written with EXISTS and NOT EXISTS subqueries, like this:

SELECT u.*
FROM shortenified_urls AS u                        -- all links
WHERE u.is_social = '1'
  AND u.active = '1'
  AND EXISTS
      ( SELECT 1
        FROM linkcats AS lc                        -- that are related
        WHERE lc.link_id = u.id                    -- to at least one of
          AND lc.cat_id IN (17,1,35,33,50)         -- these categories
      ) 
  AND NOT EXISTS                                   -- but not to
      ( SELECT 1                                  
        FROM linkcats AS lc 
        WHERE lc.link_id = u.id
          AND lc.cat_id IN (28)                    -- any of these
      )  ;