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?

Tim Biegeleisen · Accepted Answer · 2018-02-16 04:49:16Z

3

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.

edited Feb 16, 2018 at 4:49

answered Feb 16, 2018 at 4:33

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Sablefoste Over a year ago

Wow, there are a lot of concepts going on, especially in the ON clause. But I tested it, and it works! Thank you so much!

Tim Biegeleisen Over a year ago

@Sablefoste Do you want a particular ordering to the names in the CSV list?

Sablefoste Over a year ago

Yes, I now that you mention it, I was planning on adding a sort ASC by lname to the subquery. I'm not sure where that would go with the FIND_IN_SET though.

Tim Biegeleisen Over a year ago

If you decide that you want to order the CSV list, you may add an ORDER BY clause to GROUP_CONCAT.

Sablefoste Over a year ago

Thanks a bunch @Tim! I just found that as well here: stackoverflow.com/a/995383/1408137 I really appreciate your help!

Collectives™ on Stack Overflow

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

1 Answer 1

Demo

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Linked

Related