GROUP_CONCAT in sub-query based on specified values

Question

User Table:

ID       InstructionSets
 1        123,124

Instruction Set Table:

ID       Name
 123      Learning SQL
 124      Learning More SQL  

Desired Query Result:

UserID    SetID             SetNames
 1         123,124           Learning SQL,Learning More SQL

Current SQL:

SELECT U1.ID AS UserID, U1.InstructionSets AS SetID, (
    SELECT GROUP_CONCAT(Name ORDER BY FIELD(I1.ID, U1.InstructionSets))
    FROM Instructions I1
    WHERE I1.ID IN (U1.InstructionSets)
) AS SetName
FROM Users U1
WHERE `ID` = 1

RESULT

UserID   SetID             SetNames
 1        123,124           Learning SQL

As expected, if I remove the WHERE clause in the sub-query, all of the SetNames appear; but if I specify the required IDs, I only get the name associated with the first ID. Obviously, I also need to fetch the SetNames in the same order as the IDs. Hence ORDER BY in GROUP_CONCAT.

Also:

• Is there a better approach (other than storing the user instruction set assignments in a separate table — overkill for this application)? Couldn't see how to use JOIN in this situation.
• Is there a better title for this question?

Thanks.

Answer 1

Instead of IN use LIKE operator like this:

SELECT U1.ID AS UserID, U1.InstructionSets AS SetID, (
    SELECT GROUP_CONCAT(Name ORDER BY (I1.ID))
    FROM Instructions I1
    WHERE CONCAT(',', U1.InstructionSets, ',') LIKE concat('%,', I1.ID, ',%')
) AS SetName
FROM Users U1
WHERE `ID` = 1

See the demo.
Results:

| UserID | SetID   | SetName                        |
| ------ | ------- | ------------------------------ |
| 1      | 123,124 | Learning SQL,Learning More SQL |