Subquery not working in where IN clause in mysql

Question

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.

Please clarify the problem you are having. At this point you are not even selecting data from you database, you are creating a dynamic column with the replaced value of `advisor_ids` - [demo](http://sqlfiddle.com/#!9/1a74d8/1721) — DarkBee, Aug 29 '23 at 05:34
Consider modifying your database, storing multiples ID's (in a RMDB) isn't a good aproach. Conside creating a table `jobcard_per_advisor` with `jobcard_id` and `advisor_id` as columns. — DarkBee, Aug 29 '23 at 06:10
Do not work with JSON array using string functions. Use according JSON functions (for example, MEMBER OF, JSON_CONTAINS and so on). — Akina, Aug 29 '23 at 06:39
[Tips for asking a good Structured Query Language (SQL) question](//meta.stackoverflow.com/questions/271055), #5 and #3. — Akina, Aug 29 '23 at 06:41
You can begin from adding the expected output to your post: that will make everyone clear what you're expecting this query to do, and avoid "*it's not working*" situations under answers. — lemon, Aug 29 '23 at 07:00
Your problem is that the string your subquery is creating is a single string with single quotes and commas making it LOOK like the values you're giving in your working query but it's not the same. The `IN` function needs a set of values, which means your subquery needs to return multiple rows rather than just a string — Jon White, Aug 29 '23 at 07:07
Exploding a string of values in MySQL is tricky and as others have hinted, you should avoid storing related IDs in a single column. You should consider using a pivot table to link your advisors to your jobs. Then you can use a join to get all advisors for a job — Jon White, Aug 29 '23 at 07:10
You'd be looking at making a custom function to separate your comma delimited string into values, then you'd have to accept a massive performance loss over a pivot table and a join. So really the answer to your question is to restructure your DB — Jon White, Aug 29 '23 at 07:13

score 1 · Accepted Answer · answered Aug 29 '23 at 07:27

1

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

answered Aug 29 '23 at 07:27

Ergest Basha

7,870
4
8
28

1

@shambhu-sharan: Please also do note that the parameters used in `FIND_IN_SET()` are in another order than you where using.... – Luuk Aug 29 '23 at 07:42
This is what I am looking for. – Shambhu Sharan Aug 29 '23 at 07:45
Very slow. If I adding FIND_IN_SET() in my query. execution time increase from .02s to 22 sec on same record – Shambhu Sharan Aug 30 '23 at 13:23
@ShambhuSharan I already mentioned **For huge data the performance would be terrible.** , the best solution is to fix table design – Ergest Basha Aug 30 '23 at 13:30

Amit Mohanty · Answer 2 · 2023-08-29T06:39:34.087

-1

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;

edited Aug 29 '23 at 06:39

answered Aug 29 '23 at 06:04

Amit Mohanty

387
1
9

Please explain the answer - How does this solve the question? Code-only answers are not good answers – DarkBee Aug 29 '23 at 06:08
What do you mean, that's not the issue - [demo](http://sqlfiddle.com/#!9/9eecb/359089) – DarkBee Aug 29 '23 at 06:13

Subquery not working in where IN clause in mysql

2 Answers2