CODEWITHSUNDEEP
mysqlsql
Giedrius
Giedrius

Reputation: 1390

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!

Upvotes: 0

Views: 37

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269733

The simplest way to do this in MySQL is to use a HAVING clause after the GROUP BY. The simplest such clause is:

HAVING MAX(ngm.members_group_id = 4) > 0

Upvotes: 0

Related Questions