CODEWITHSUNDEEP
mysqlsqlsubquerywhere-clause
Vencarii
Vencarii

Reputation: 45

MySQL query with sub-select in IN()-clause gives not enough results

I built a sql-query and don't get the results I expect. I try to explain what my steps to the sql-statement were.

First I have the sub-select that I use in the IN()-clause:

SELECT GROUP_CONCAT(job_id SEPARATOR ',') as job_ids
FROM candidate_job
WHERE candidate_id = 1111
GROUP BY candidate_id

The result is 1,7,37,38,39,135,136 and that's exactly what I expect. Then I use these concenated numbers in my outer sql-statement:

SELECT GROUP_CONCAT(DISTINCT(user_id) SEPARATOR ',') as user_ids
FROM user_job
WHERE job_id IN (1,7,37,38,39,135,136)

This gives me 139,539,1686,1759 and this is also exactly what I expect.

Now I combine both queries, the first one is the sub-select for the IN()-clause of the second one:

SELECT GROUP_CONCAT(DISTINCT(user_id) SEPARATOR ',') as user_ids
FROM user_job
WHERE job_id IN (
    SELECT GROUP_CONCAT(job_id SEPARATOR ',') as job_ids
    FROM candidate_job
    WHERE candidate_id = 1111
    GROUP BY candidate_id
)

The result is only 139,539 and I don't understand why it's not 139,539,1686,1759 as it should be.

Does anyone have an idea why this could be the case? Use a GROUP_CONCAT() column in IN() shouldn't be so rare, right?

Upvotes: 0

Views: 55

Answers (2)

forpas
forpas

Reputation: 164139

The operator IN does not work with strings that are comma separated values.
What you want is FIND_IN_SET():

SELECT GROUP_CONCAT(DISTINCT user_id) as user_ids
FROM user_job
WHERE FIND_IN_SET(
        job_id,  
        (SELECT GROUP_CONCAT(job_id) FROM candidate_job WHERE candidate_id = 1111)
)

SEPARATOR ',' is not needed because ',' is the default separator.

Upvotes: 1

GMB
GMB

Reputation: 222582

group_concat() gives you a comma-separated string; passing this as an operand to operator in does not do what you want (it ends up searching for an exact match).

I would recommend exists:

SELECT GROUP_CONCAT(DISTINCT(user_id) SEPARATOR ',') as user_ids
FROM user_job uj
WHERE EXISTS (
    SELECT 1
    FROM candidate_job cj
    WHERE cj.candidate_id = 1111 AND uj.job_id = cj.job_id
)

Upvotes: 1

Related Questions