TransWikia.com

MySQL subquery with WHERE filters not working as expected

Database Administrators Asked by HDer on December 22, 2021

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

One Answer

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

Answered by HDer on December 22, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP