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?