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

MySQL aggregated records

I have 2 tables:

table father:

ID  NAME
1   John
2   Paul
3   Mike

and table child

ID  FATHER  CHILD
1   1       Mary
2   2       Liz
3   2       Lucy

now I need a view family like:

FATHER_ID FATHER_NAME  CHILDREN
1         John         'Mary'
2         Paul         'Liz, Lucy'
3         Mike         NULL

What’s the best solution?

Tags:

2

Answers


  1. 0

    This could be a better

    CREATE VIEW family AS
SELECT 
    f.ID AS FATHER_ID,
    f.NAME AS FATHER_NAME,
    GROUP_CONCAT(c.CHILD) AS CHILDREN
FROM
    father f
LEFT JOIN
    child c ON f.ID = c.FATHER
GROUP BY
    f.ID, f.NAME;
    Login or Signup to reply.
  2. 0

    An option here:

    SELECT f.id AS FATHER_ID, 
f.name AS FATHER_NAME,
GROUP_CONCAT(c.CHILD SEPARATOR ', ') AS CHILDREN
FROM father as f
LEFT JOIN child c ON c.FATHER = f.id
GROUP BY (f.id)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search