MYSQL WHERE field IN GROUP_CONCAT()

user_152099 0
Viewed 133

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

group-concat mysql
1 Answers

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
user_104988280
Related