Count how many records belong to 2 categories in a n to n relation I have a database containing articles whose schema is more or less the following:

COLLECTIONS ( PK id )
&gt; 1 : n &gt;
TOPICS ( PK id, FK collection_id )
&gt; n : n &gt;
TOPICS_TO_ARTICLES( FK topic_id, FK article_id )
&gt; n : n &gt;
ARTICLES ( PK id)


What I need is to find a way to understand how articles and collections are related, and one of the metrics is "how many articles belong to "collection 1" AND "collection 2". I thought I found a working query, but I think I'm missing something. Here is my query:

SELECT
  COUNT(*) AS total
FROM
  topics_to_articles AS TTA
INNER JOIN
  topics AS T ON T.id = TTA.topic_id
INNER JOIN
  articles AS A ON TTA.article_id = A.id
WHERE
  T.collection_id = cat_1_id AND A.status = 1 AND TTA.article_id IN(
  SELECT
    TTA2.article_id
  FROM
    topics_to_articles AS TTA2
  INNER JOIN
    topics AS T2 ON T2.id = TTA2.topic_id
  INNER JOIN
    articles AS A2 ON TTA2.article_id = A2.id
  WHERE
    T2.collection_id = cat_2_id AND A2.status = 1
)


I was pretty sure about this query being ok, but I noticed that if I invert the two "collection_id" variables (like c1 = 1 and c2 = 2 and then c2 = 1 and c1 = 2) I obtain two different results.

Is there a way to get that info?
When you join topics to topics_to_articles to articles, each article may be tied to multiple topics. And, multiple topics may be associated with each collection. So, you may be counting the same article multiple times.

For example:


Collection A is tied to topics a,b,c
Collection B is tied to topics b,d,e
Article 1 is tied to topics a,b,e
Article 2 is tied to topics a,c
Article 3 is tied to topics b, d, e


If cat_1_id is 1 and cat_2_id is 2:


the IN clause will contain article IDs 1 and 3 (while the query will list them more than once, the IN clause doesn't care how many times an ID is listed, just whether it's there or not)
The main query will count:


article 1, topic a
article 1, topic b
article 1, topic c
article 3, topic b



If cat_1_id is 2 and cat_2_id is 1:


the IN clause will contain article IDs 1, 2, and 3
The main query will count:


article 1, topic b
article 1, topic e
article 3, topic b
article 3, topic d
article 3, topic e



The first query gives you 4; the second query gives you 5; you should really get 2 (because there are two distinct articles returned).

And, that word is the solution - DISTINCT

Change your two lines to:

SELECT
  COUNT(DISTINCT A.article_id) AS total


This way, when the same article_id shows up multiple times, it will still only be counted once.