2

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?

3
  • 2
    Please create a dbfiddle.uk with some sample data, and update your post with the url and expected result. Aug 5, 2022 at 10:44
  • Giving table names without their definitions and example data is like telling a mechanic what kind of car you have without letting them look under the hood to find the problem. Please update your post with your table definitions, example data, and tag what version of MySQL you're using.
    – J.D.
    Aug 5, 2022 at 12:39
  • Do you want the immediate parents? Or all ancestores? Please provide SHOW CREATE TABLE and the MySQL version number.
    – Rick James
    Aug 5, 2022 at 22:39

1 Answer 1

0

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 ; 
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.