mysql: inner join with in and not in

Question

I'm trying to pull rows from one table "articles" based on specific category tags from table "article_category_reference", to exclude articles that have a specific tag. I have this query right now:

SELECT DISTINCT
    a.article_id,
    a.`title`,
    a.`text`,
    a.`date`
FROM
    `articles` a
INNER JOIN `article_category_reference` c ON
    a.article_id = c.article_id AND c.`category_id` NOT IN (54)
WHERE
    a.`active` = 1 
ORDER BY
    a.`date`
DESC
LIMIT 15

The problem is, it seems to grab rows even if they do have a row in the "article_category_reference" table where "category_id" matches "54". I've also tried it in the "where" clause and it makes no difference.

Keep in mind I'm using "NOT IN" as it may be excluding multiple tags.

SQL fiddle to show it: http://sqlfiddle.com/#!9/b2172/1

Tables:

CREATE TABLE `article_category_reference` (
  `ref_id` int(11) NOT NULL,
  `article_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `articles` (
  `article_id` int(11) UNSIGNED NOT NULL,
  `author_id` int(11) UNSIGNED NOT NULL,
  `date` int(11) NOT NULL,
  `title` varchar(120) NOT NULL,
  `text` text CHARACTER SET utf8mb4 NOT NULL,
  `active` int(1) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Answer 1

One option is to use an EXISTS clause:

SELECT DISTINCT
    a.article_id,
    a.title,
    a.text,
    a.date
FROM articles a
WHERE
    a.active = 1 AND
    NOT EXISTS (SELECT 1 FROM article_category_reference c
                WHERE a.article_id = c.article_id AND c.category_id = 54)
ORDER BY
    a.date DESC
LIMIT 15;

The logical problem with your current approach of checking the category in the WHERE clause is that it is checking individual records. You need to assert that all category records for a given article, in aggregate, do not match the category you wish to exclude. An EXISTS clause, as I have written above, is one way to do it. Using GROUP BY in a subquery is another way.