I have a couple of tables and each one has a many to many relationship,
Lets say 'parents'
'Name', 'email', 'chid_id'
'Chris Pan', 'chris@email.com', 1
'Jane Pan', 'chris@email.com', 2
'Sally Jenkins', 'sally@email.com', 3
Then 'children'
'id', 'first_name', 'last_name',
1. 'Peter', 'Pan'
2. 'Wendy', 'Wilson'
3. 'Sally Jnr', 'Jenkins'
I want to query to get all parents, grouped by email, and their children.
SELECT GROUP_CONCAT(parents.name) AS parents, LOWER(email) AS email,
(SELECT GROUP_CONCAT(CONCAT(children.first_name, ' ', children.last_name)) AS childrens_names
FROM children WHERE children.id IN ( parents.child_id )
FROM parents GROUP BY email
Issue:
This will only get ONE children record, some times there will only be one parent, or one child, but often there are 2 (or more) of each.
I have tried GROUP_CONCAT() in the subquery WHERE
WHERE children.id IN ( GROUP_CONCAT(parents.child_id) )
But this didn't return any children records.
Summary - I want to get and join 1 or more rows on two tables grouped by the parent email and joined by the child_id
Thanks
Join properly the tables and aggregate:
SELECT p.email,
GROUP_CONCAT(p.name) AS parents,
GROUP_CONCAT(CONCAT(c.first_name, ' ', c.last_name)) AS childrens_names
FROM parents p LEFT JOIN children c
ON c.id = p.child_id
GROUP BY p.email
If there is a case of duplicate children names then use DISTINCT inside GROUP_CONCAT():
GROUP_CONCAT(DISTINCT CONCAT(c.first_name, ' ', c.last_name)) AS childrens_names