[mysql] where query count result > 0

Question

Hi all, I need to do a query like this:

SELECT wposts.ID FROM posts wposts WHERE ( SELECT COUNT(ID) FROM surveys WHERE POST_ID = wposts.ID) > 0

but need it to be working ?

Answer 1

Select returns by default a "set", which is not compatible with an integer. You can do something like:

SELECT wposts.ID FROM posts wposts WHERE 0 NOT IN ( SELECT COUNT(ID) FROM surveys WHERE POST_ID = wposts.ID)

Answer 2

The following SQL works fast, if the foreign key surveys.POST_ID is indexed.

SELECT wposts.ID FROM posts wposts
    INNER JOIN surveys ON surveys.POST_ID = wposts.ID
GROUP BY wposts.ID