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?