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

John Woo · Accepted Answer · 2013-03-21 08:40:16Z

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, 2013 at 8:40

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Strawberry Over a year ago

'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)

John Woo Over a year ago

@Strawberry that's why I used better, didn't say best :D

ml_chai · Accepted Answer · 2020-07-16 07:00:24Z

3

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')
);

edited Jul 16, 2020 at 7:00

ml_chai

711 silver badge5 bronze badges

answered Apr 17, 2019 at 12:50

bdzzaid

9189 silver badges16 bronze badges

Collectives™ on Stack Overflow

Using NOT IN with GROUP CONCAT in MYSQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related