CODEWITHSUNDEEP
mysqlgroup-concat
Alan M.
Alan M.

Reputation: 1369

GROUP_CONCAT in sub-query based on specified values

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:

Thanks.

Upvotes: 0

Views: 63

Answers (2)

spencer7593
spencer7593

Reputation: 108500

We can use FIND_IN_SET(). In this context, using FIELD() function doesn't make sense.

We can also use FIND_IN_SET() in the WHERE clause. (Function returns 0 when the string isn't found in the string list.)

e.g.

SELECT u.id               AS userid
     , u.instructionsets  AS setid
     , ( SELECT GROUP_CONCAT(i.name ORDER BY FIND_IN_SET(i.id, u.instructionsets))
           FROM `Instructions` i
          WHERE FIND_IN_SET(i.id, u.instructionsets))
       ) AS setname
 FROM `Users` u
WHERE u.id = 1

Storing comma separated lists is an anti-pattern; a separate table isn't overkill.

Assuming id is unique in Users table, we could do a join operation with a GROUP BY 

SELECT u.id                   AS userid
     , MIN(u.instructionsets) AS setid
     , GROUP_CONCAT(i.name ORDER BY FIND_IN_SET(i.id, u.instructionsets))) AS setname
 FROM `Users` u
 LEFT
 JOIN `Instructions` i
   ON FIND_IN_SET(i.id, u.instructionsets)
WHERE u.id = 1
GROUP BY u.id

Upvotes: 1

forpas
forpas

Reputation: 164194

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 |

Upvotes: 1

Related Questions