Get all children for a set of parents that has a specific child

Question

I have a parent and child table linked by a linked/associative table (since the parent - child is many to many relationships.

I am looking to get a list of all parents that has a specific child BUT also list/group all children by parent. Example result if searching for data where ChildD exists:

ParentA -> ChildD, ChildR, ChildT
ParentD -> ChildD, ChildE
ParentX -> ChildD, ChildY, ChildW, ChildP

This is the query I currently have (psuedo):

SELECT T1.Name, GROUP_CONCAT(T3.Title) FROM MainTable AS T1
INNER JOIN between MainTable and LinkedTable (T2)
INNER JOIN between LinkedTable and ChildTable (T3)
WHERE T3.Id = 1
GROUP BY ...

However, this just brings back a list of parent with the single child with Id =1) listed against them. I have experimented with select sub queries and other types of joins but without success. How do I get all the Children for each parent grouped by parent? Many thanks, Chris

Thanks Phil. I used your example (see below) but it still only return the single Child (with id 209) for each parent. The GROUP_CONCAT makes the single child being repeated 4 times in the response e.g. ChildD,ChildD,ChildD,ChildD. The correct number of children for the given parent is 4 (but the response should be 4 different children).

SELECT T3.ParentName, GROUP_CONCAT(T1.`Title`)
        FROM `refSubjectTags` AS T1
        INNER JOIN `linkMetricParentsToSubjectTags` T2
        ON T1.SubjectTags_Id = T2.linkMetricParentsToSubjectTags_SubjectTags_Id
        AND T1.SubjectTags_Id = 209
        INNER JOIN `datMetricParents` AS T3
        ON T3.MetricParents_Id = T2.linkMetricParentsToSubjectTags_MetricParents_Id
        INNER JOIN linkMetricParentsToSubjectTags AS T4
        ON T3.MetricParents_Id = T4.linkMetricParentsToSubjectTags_MetricParents_Id
        INNER JOIN refsubjecttags AS T5
        ON T5.SubjectTags_Id = T4.linkMetricParentsToSubjectTags_SubjectTags_Id
        GROUP BY T3.ParentName, T1.Title

The version is: 8.0.18

I want the parents and all children of each parent BUT limited by the parents -> children where the children contains a certain child / row id.

In this case I want a list of all Economic metrics (parents) and all their subject tags (children) but limited to only those combinations where a certain subject tags exists. Imagine that you have a subject tag "Gross domestic product" and want a list of all economic metrics with this tag but ALSO a list all other tags that those metrics have.

The code I currently have will list all the parents/metrics with the select tag but does not list the other tags of those metrics (see query above).

Do you need the create code for all relevant tables?

Answer 1

To list the Parents and Children for any Parent that has 'ChildD'?
Something like this, perhaps?

select 
from children c1
inner join link_table l1 
   on c1.id = l1.child_id
   and c1.id = 'ChildD' 
inner join parents p1 
   on l1.parent_id = p1.id    <-- this far gets you any parents with 'ChildD'
inner join link_table t2 
   on p1.id = l2.parent_id 
inner join children c2 
   on l2.child_id = c2.id     <-- This far gets you all the children of those parents
order by p1.id, c2.id ;