Giedrius Giedrius - 2 months ago 12
SQL Question

MySQL - Checking multiple values with IN clause from LEFT JOIN result

How can I use result from a

LEFT JOIN
in an
IN
clause?

This is my query so far:

SELECT
n.id,
n.title,
n.public,
CAST(GROUP_CONCAT(DISTINCT ngm.members_group_id) AS CHAR(1000)) AS `news_groups_text`,
GROUP_CONCAT(DISTINCT ngm.members_group_id) AS `news_groups`

FROM news n

LEFT JOIN news_groups_map ngm ON ngm.news_id = n.id

WHERE public=0

GROUP BY n.id


Which returns results in form

id title public news_groups_text news_groups
159 Test 0 5,6,4 (BLOB) 5 bytes


How can I add a clause that checks which groups the article belongs to? For example, I have groups 4 and 6, I need to return all results that have at least one of them in
news_groups
.

I am trying to get to check one group with
IN
clause, by adding this to the
WHERE
clause:

WHERE public=0 AND
4 IN (GROUP_CONCAT(DISTINCT ngm.members_group_id))


But then i get error
[Err] 1111 - Invalid use of group function


How can I filter out the articles by groups? If I could check at least one group I could just chain them with
AND


Thanks!

Answer

I think you are looking for FIND_IN_SET:

FIND_IN_SET(4, news_groups_text)

Note: This can be used only by an outer query that uses the original query as a subquery.

But it is more natural to place the condition in the HAVING clause:

HAVING COUNT(CASE WHEN news_groups_text=4 THEN 1 END) > 0
Comments