Question 219552 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL query to join comma separated values

Hi have a table that has Owner ID which is a array of users. Below is my tables.

Task table

ID    Task_Name   Task_Owner
1      Create      1,3,4
2       Edit       2,1,3
3      Delete      2,1,4

Owner table:

ID        Owner_Name
1           Mike
2           Ken
3           Lim
4           Nick

And I need an output that fetch all names

Output:

ID    Task Name      Owners
1     Create         Mike,Lim, Nick
2     Edit           Ken, Mike, Lim
3     Delete         Ken, Mike, Nick
Tags:

2

Answers


  1. 0

    It is not a good practice, you should normalize your database by creating other table with the relationship, or use the next code, just replace for the correct name of your columns and table, Also see Solution

    select
m.id,
m.taskname
group_concat(c.ownername) from
tasktable m
join ownertable c on find_in_set(c.id, m.taskowner) group by
m.id
    Login or Signup to reply.
  2. 0

    If you are looking for something that will run on Microsoft SQL Server then the previous example (using group_concat) will not run as that function is unavailable on that platform. This should meet your requirements:

    SELECT
  t.ID,
  t.Task_Name AS [Task Name],
  STRING_AGG(o.Owner_Name, ', ') AS Owners
FROM
  [Task_Table] t
JOIN
  [Owner_Table] o ON o.ID IN 
    (
        SELECT [value] FROM STRING_SPLIT(t.Task_owner, ',')
    )
GROUP BY
  t.ID, 
  t.Task_Name
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search