In a single MySQL query, can I return a string array from a field with an array of primary keys

Question

Using MySQL, if I have a table Topic with the field who that uses the primary key uid from the table Users:

| aid |  subject |  who    |
---------------------------
| 1   |  foo     | 1, 2, 3 |
| 2   |  bar     | 1, 3    |
| 3   |  spec    | 1, 2, 4 |
| 4   |  sauce   | 3       |

and a table Users:

 | uid |  fname  |  lname  |
 ---------------------------
 | 1  |  Jack    | Black   |
 | 2  |  Jill    | White   |
 | 3  |  Tom     | Grey    |
 | 4  |  Dick    | Brown   |

and I want to query who is associated with each subject, by name, my result should look like:

 | aid |  subject |  who    
    1      foo      Jack Black, Jill White, Tom Grey
    2      bar      Jack Black, Tom Grey
    3      spec     Jack Black, Jill White, Dick Brown
    4      sauce    Tom Grey

I thought the query should look something like:

SELECT GROUP_CONCAT(namearray SEPARATOR ', ') 
FROM 
  (SELECT CONCAT(fname, ' ', lname) AS namearray 
   FROM Users 
   WHERE uid IN (1,2,3) ORDER BY lname) AS tWho

which works when manually entering the 1,2,3 in the IN clause. I don't even know where to begin to insert the field who into the IN clause.

Can this be done in one query? Or will I need to get the query results with the who numbers, and perform a second query to get the final result?

Answer 1

Try using FIND_IN_SET to join:

SELECT
    t.aid,
    t.subject,
    GROUP_CONCAT(u.fname, ' ', u.lname) AS who
FROM Topic t
LEFT JOIN Users u
    ON FIND_IN_SET(u.uid, REPLACE(t.who, ' ', '')) > 0
GROUP BY
    t.aid,
    t.subject;

Demo

Conceptually, the above query works, for example, by joining the foo topic CSV list 1, 2, 3 to the user table. This results in the following intermediate result:

aid | subject | who | full name
1   | aid     | 1   | Jack Black
1   | aid     | 2   | Jill White
1   | aid     | 3   | Tom Grey

We then group concatenate by topic to end up again with a single record for that topic along with a CSV string of all matching users.

One other note is that we need to remove spaces from your who CSV string in order for FIND_IN_SET to work as expected, hence you see a call to REPLACE inside the query.