Mysql query get results like tuple

Question

I have these tables :

work --> id, name, description

subwork --> id, name , work_id

which work_id is foreign key for work table .

Im using this query to get results :

("select work.*,  subwork.name from work"
                       "inner join subwork on subwork.work_id = work.id")

this will return the repetitive values , on the other hand I need all of data in result so distinct is not helpful . I wanna know is there any way to get results like this :

(work_id, work_name, work_desc, (subwork_name1, subwork_name2, subwork_name3,... ))

score 0 · Accepted Answer · answered Sep 18 '18 at 22:32

0

You can get it as a string, if you use group_concat(). Maybe that helps too.

SELECT w.id,
       w.name,
       w.description,
       group_concat(s.name SEPARATOR ', ')
       FROM work w
            INNER JOIN subwork s
                       ON s.work_id = w.id
       GROUP BY w.id,
                w.name,
                w.description;

answered Sep 18 '18 at 22:32

sticky bit

36,626
12
31
42

YES , now subwork names can split by ', ' and convert to a list . awesome – Mehdi bahmanpour Sep 18 '18 at 22:53

Mysql query get results like tuple

1 Answers1