CODEWITHSUNDEEP
mysqlsubquerygroup-concat
George
George

Reputation: 121

use result from subquery with comma separated list in "IN" clausule of main query

I have 2 tables: - one (users) containing the initials for each user in a column called login - another one (taskplanned) containing the same initials in a column named userId. I need to select all users from the table "users" matching the criteria "technician" and then I need to select all tasks from taskplanned matching those users.

I've build following query :

SELECT
tp.*
FROM
taskplanned tp
WHERE
tp.userId IN (
    SELECT
        (
            GROUP_CONCAT(CONCAT('''', login, ''''))
        )
    FROM
        users u
    WHERE
        u.cis_role LIKE '%technician%'
)

When running the subquery standalone, it shows this : 'BDG','FG','BJ','WDG','NDW','TV','SA','JV','GW','MU','KD','LV','KL','TDW','DK','KDS','PD','WB'

So, I would assume that when inserting this part in the main query's "IN" clausule it should work. Unfortunatly it's not. Am I missing something here?

If I take the result from the subquery and add it manualy to the main query, it works like a charm..

Upvotes: 1

Views: 164

Answers (2)

Mohamed AIT MANSOUR
Mohamed AIT MANSOUR

Reputation: 845

Mysql SELECT * FROM taskplanned WHERE userId IN ( SELECT login FROM users WHERE cis_role LIKE '%technician%')

you do not need group concat..

Upvotes: 0

fancyPants
fancyPants

Reputation: 51868

GROUP_CONCAT() is not necessary. Just do it like this:

SELECT
tp.*
FROM
taskplanned tp
WHERE
tp.userId IN (
    SELECT
        login
    FROM
        users u
    WHERE
        u.cis_role LIKE '%technician%'
)

When this is a one to one relationship, a better approach is joining:

SELECT
tp.*
FROM
taskplanned tp
JOIN users u ON tp.userId = u.login
WHERE u.cis_role LIKE '%technician%'

Upvotes: 1

Related Questions