Question 240125 in Mysql Question posted in
The official MySQL documentation can be found here.

Subquery not working in where IN clause in mysql

Jobcard table
jobcardId advisorId
f82d6c76-b344-4f58-8fe9-a405c9b968d1 [5d414796-935d-414d-8f7a-952c7806d7e3,627433fe-b6ca-465e-be66-f53cbc3c6d86] 
User Table
id name
5d414796-935d-414d-8f7a-952c7806d7e3 Adam
627433fe-b6ca-465e-be66-f53cbc3c6d86 Martin
b6ca796t-judk-djdj-djdj-didkdkdksssk Marry

Expected Output

f82d6c76-b344-4f58-8fe9-a405c9b968d1 Adam,Martin

I have tried below query

First way

SELECT GROUP_CONCAT(U.name) name
FROM jobcards J
JOIN users U ON FIND_IN_SET(J.advisor_ids, U.id)
GROUP BY J.id;

Second Way

I have formatted advisor_id so that I can use advisor ID in where IN clause like below.

SELECT replace(replace(replace(advisor_ids,'[',"'"),']',"'"),",","','") AS id from jobcards where id ='f82d6c76-b344-4f58-8fe9-a405c9b968d1'

Giving result
‘5d414796-935d-414d-8f7a-952c7806d7e3′,’627433fe-b6ca-465e-be66-f53cbc3c6d86’

If I am using above ID in query directly, it’s giving result.

SELECT * FROM users U WHERE id IN('5d414796-935d-414d-8f7a-952c7806d7e3','627433fe-b6ca-465e-be66-f53cbc3c6d86')

But when I am using first query in where IN clause than not giving result.

SELECT * FROM users U WHERE id IN(SELECT replace(replace(replace(advisor_ids,'[',"'"),']',"'"),",","','") AS id from jobcards where id ='f82d6c76-b344-4f58-8fe9-a405c9b968d1')

Not giving result.

Tags:

2

Answers


  1. 0

    You can create a dynamic query to get the result:

    SET @subqueryResult = (
    SELECT 
        REPLACE(REPLACE(REPLACE(advisor_ids, '[', ''''), ']', ''''), ',', ''',''') AS id 
    FROM 
        jobcards 
    WHERE 
        id = 'f82d6c76-b344-4f58-8fe9-a405c9b968d1'
);

SET @dynamicQuery = CONCAT('
    SELECT 
        * 
    FROM 
        users U 
    WHERE 
        id IN (', @subqueryResult, ')
');

PREPARE finalQuery FROM @dynamicQuery;
EXECUTE finalQuery;
DEALLOCATE PREPARE finalQuery;
    Login or Signup to reply.
  2. 0

    See Is storing a delimited list in a database column really that bad? and please fix the design , you will be facing a lot of issues mostly performance in the future.

    As per the question, if advisorId column contains the string separated by comma and the [] symbols a simple join with find_in_set and replace would give you the desired result.

    For huge data the performance would be terrible.

    SELECT jobcardId, GROUP_CONCAT(U.name) name
FROM jobcards J
JOIN users U ON FIND_IN_SET(U.id,replace(replace(J.advisorId,'[',''),']',''))
GROUP BY J.jobcardId;

    Result

    jobcardId                                   name
f82d6c76-b344-4f58-8fe9-a405c9b968d1    Adam,Martin

    See example

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search