Using NOT IN with GROUP CONCAT in MYSQL

group-concat, mysql, sql, subquery

Solution

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

Problem

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

Original source