MySQL subquery with WHERE filters not working as expected

Question

I have a question/tagging system like StackExchange. I want to display the tags associated with each question and also show the total number of times the tag is used, unless the question has been taken offline or is suspended, then it is not included in the total number count.

Tables are: QUESTIONS - includes fields: suspended and offline TAGS - includes fields: tag_id and tag_name TAGS_X - includes fields: tag_id, question_id

The query below almost works, but the subquery seems to return the total count of times the tag is used and the filtering on the suspended and offline fields is not functioning as I intended (does not seem to filter on those conditions).

SELECT tags_x.tag_id, tags.tag_name, tags_x.question_id, questions.suspended, questions.offline, 
(select count(tags_x.tag_id) from tags_x WHERE tags_x.tag_id=tags.tag_id AND questions.suspended = 0 AND questions.offline = 0) num
from tags_x
LEFT JOIN tags ON tags.tag_id = tags_x.tag_id  
LEFT JOIN questions ON questions.question_id = tags_x.question_id 
WHERE questions.suspended = 0 AND questions.offline = 0

Below shows a typical result. Tag 'a' actually shows up in 21 rows. One row is filtered where the offline value is 1. The subquery count is returning 22, but I really want it to show the filtered result of 21. Seems like the WHERE/(offline, suspended) filters in the subquery are incorrectly applied.

Can you help me out to determine the correct way to do this?

Answer 1

Seems like I had to use a larger subquery and join it with the main query. Works fine this way. Not sure why it did not work the above way though...

SELECT tags_x.tag_id, tags.tag_name, questions.question_id,X.num
FROM tags_x
LEFT JOIN tags ON tags.tag_id = tags_x.tag_id  
LEFT JOIN questions ON questions.question_id = tags_x.question_id 
LEFT JOIN 
(SELECT tags_x.tag_id, count(tags_x.tagsx_id) AS num
FROM tags_x
LEFT JOIN questions ON tags_x.question_id = questions.question_id
LEFT JOIN tags ON tags.tag_id = tags_x.tag_id
WHERE questions.suspended =0 AND questions.offline = 0 AND tags.tag_name IS NOT NULL
GROUP BY tags.tag_id) AS X ON X.tag_id=tags.tag_id  
WHERE questions.question IS NOT NULL
ORDER BY `questions`.`question_id` ASC

Answer 2

In your subquery counting from tags_x you refer to questions.offline=0. The instance of tags_x in the subquery has nothing to do with the tags_x used on the main/outer query; in other words the subquery is not joined to questions, and the criteria mentioned above has no effect on the subquery. Consider this alternative:

SELECT 
   t_x.tag_id
 , t.tag_name
 , tx.question_id
 , q.suspended
 , q.offline
 , (SELECT count(*) 
    FROM tags_x tx_a
         inner join
         questions q_a ON q_a.question_id = tx_a.question_id 
    WHERE tx_a.tag_id=t.tag_id 
          AND q_a.suspended = 0 AND q_a.offline = 0) num
from tags_x tx
     LEFT JOIN tags t ON t.tag_id = tx.tag_id  
     LEFT JOIN questions q ON q.question_id = tx.question_id 
WHERE q.suspended = 0 
  AND q.offline = 0

I used table aliases to make references more apparent. I used _a to distinguish table instances used in the subquery.

Also, your LEFT JOIN to questions has no difference from INNER JOIN because you then haveWHERE questions.suspended = 0 which would eliminate any rows that would normally come from tags_x with no matching row in questions (question.suspended is NULL).