1

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!

1
  • 1
    I think what you want is a MINUS operator (doesn't exist, but can be emulated), but an example with a very small set of original data, expected results and obtained results will help us a lot helping you.
    – jynus
    Mar 4, 2015 at 14:11

1 Answer 1

3

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
      )  ;
2
  • 1
    I like more the LEFT JOIN...IS (NOT) NULL option, but of course that depends on the number of rows and MYSQL version.
    – jynus
    Mar 4, 2015 at 14:29
  • 1
    @jynus the NOT EXISTS part can be rewritten with LEFT JOIN / IS NULL, yes, and it may have better (or worse) efficiency depending on version, number of rows, data distribution, etc. The EXISTS part can be written with INNER JOIN but then it requires a GROUP BY u.id (as the OP's query) and I think that's usually less efficient. Mar 4, 2015 at 14:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.