Question: 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`**](http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_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

```
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

```