Using NOT IN with GROUP CONCAT in MYSQL

Question

I'am using the "GROUP_CONCAT" function with the "NOT IN" statement in my mysql query. But for unknown reason, it doesn't return the correct values:

Here is my query not working:

select firstname, lastname
from t_user
where (status_Id NOT IN(Select GROUP_CONCAT(id) from t_status where code = 'ACT'
or code = 'WACT'))

Returns 46 rows

Here is my query working:

select firstname, lastname
from t_user
where (status_Id NOT IN(1,4))

Returns 397 rows

The results of the of the GROUP_CONCAT subquery

 (Select GROUP_CONCAT(id) from t_status where code = 'ACT' or code = 'WACT') = 1,4.

It seems that the query take only care of the first item return by the GROUP_CONCAT subquery.

So I don't understand what's going on and why I do not have the same results in both cases.

Thanks in advance Gael

score 5 · Answer 1 · answered Mar 21 '13 at 08:40

5

in this case, you don't need to use GROUP_CONCAT function because it returns a string value. AND
1, 4 is very different from 1 and 4.

select  firstname, lastname
from    t_user
where   status_Id NOT IN 
        ( Select id 
          from t_status 
          where code = 'ACT' or code = 'WACT'
        )

and a better way to right the query is by using LEFT JOIN,

SELECT  a.firstname, a.lastname
FROM    t_user a
        LEFT JOIN t_status b
            ON a.t_status = b.id AND
                b.code IN ('ACT', 'WACT')
WHERE   b.id IS NULL

answered Mar 21 '13 at 08:40

John Woo

258,903
69
498
492

'and a better way'... hm, these days I'm not so sure. NOT EXISTS is PDQ! (Although on a purely aesthetic level, I completely agree) – Strawberry Mar 21 '13 at 09:39
@Strawberry that's why I used `better`, didn't say `best` :D – John Woo Mar 21 '13 at 09:40

score 3 · Answer 2 · edited Jul 16 '20 at 07:00

It is possible to exploit the list generated from GROUP_CONCAT as your question asks. Thus, in general, you can use the list from GROUP_CONCAT as follows:

According to the specification, you can simply use FIND_IN_SET instead of NOT IN to filter a field by subquery

select firstname, lastname
from t_user
where NOT FIND_IN_SET(status_Id, 
    (Select GROUP_CONCAT(id) from t_status where code = 'ACT' or code = 'WACT')
);

Using NOT IN with GROUP CONCAT in MYSQL

2 Answers2