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?
SHOW CREATE TABLE
and the MySQL version number.