0

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?

enter image description here

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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