Join on a string with individual values inside commas

Question

I have a table (called toy table) which shows for each toy type the sales ID processed, with commas between different sales IDs. Please note that my data schema is not this way. But I aggregate data on the toy level, to generate a report.

|------------------------|------------------------|
|         Toy            |           IDs          |
|------------------------|------------------------|
|       Buzz Lightyear   |        22,33,44        |
|       Woody            |        24,41           |
|------------------------|------------------------|

I have another table (called status table) which has status for each order ID.

|------------------------|------------------------|
|         ID             |           Status       |
|------------------------|------------------------|
|       22               |        running         |
|       33               |        paused          |
|       44               |        running         |
|       24               |        cancelled       |
|       41               |        finished        |
|------------------------|------------------------|

I want to make a table (through a join) in which I can have a column for status separated by commas, in the right order of sales IDs, and also a column for running IDs only. It would look like this:

|------------------------|------------------------|------------------------|-----------------------|
|         Toy            |           IDs          |         Status         |     Running IDs.      |
|------------------------|------------------------|------------------------|-----------------------|
|       Buzz Lightyear   |        22,33,44        |running, paused, running|     22,44             |
|       Woody            |        24,41           |   cancelled, finished  |                       |
|------------------------|------------------------|------------------------|-----------------------|

I dont know how to make the join. I've tried finding the position of ID through find_in_set() but I just can't seem to progress after that. I have to report on the toy level so there cannot be more than one toy in a row in the final dataset.

Read ["Is storing a delimited list in a database column really that bad?"](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad?r=SearchResults&s=1|192.2446) and fix the schema. — sticky bit, Apr 13 '20 at 01:11
my database is does not have a deliminited list. But its when I aggregate on a toy level I get it that way. I use (group by toy) and group_concat(distinct IDs) — Hamza Khalid, Apr 13 '20 at 01:14
Join the status table to your statement, and `ON statuses.id = toys.status` (so there's a label per row). Then group concat the status labels — Rogue, Apr 13 '20 at 01:17

score 1 · Accepted Answer · answered Apr 13 '20 at 01:36

You need to do your JOINs before you do the aggregation. Without seeing your table structures it's hard to be 100% certain, but the query you want should look something like this:

SELECT t.Toy,
       GROUP_CONCAT(o.ID ORDER BY o.ID) AS IDs,
       GROUP_CONCAT(s.Status ORDER BY s.ID) AS Status,
       GROUP_CONCAT(CASE WHEN s.Status = 'running' THEN s.ID END) AS `Running IDs`
FROM toys t
JOIN orders o ON o.Toy_ID = t.ID
JOIN status s ON s.ID = o.ID
GROUP BY t.Toy

Demo on SQLFiddle

Join on a string with individual values inside commas

1 Answers1