MYSQL WHERE field IN GROUP_CONCAT()

Question

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

re "I want to query to get all parents, grouped by email, and their children." if you have different parents with the same email, you can either group by email or get all parents, you cannot do both — ysth, Jul 17 '20 at 21:48

forpas · Accepted Answer · 2020-07-17T22:04:17.240

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

MYSQL WHERE field IN GROUP_CONCAT()

1 Answers1