How do I sort parent record based on child record values

Ephraim Wangoya
Ephraim Wangoya used Ask the Experts™
on
I need to sort parent records based on the child records, consider the tables
Parent table
ID   Name
1    One
2    Two
3    Three

Child Table
ParentID  Name
1         A
1         D
1         F
2         A
2         G
2         E
3         A
3         Z
3         B
3         Q


Result Sorted
ID   Name      [Child Records]
3    Three           A, B, Q, Z
1    One           A, D, F
2    Two            A, E, G

Sort is only necessary up to three child records but not limited
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
Hello ewangoya,

If the Child table is laid out as above, then with respect, that is a downright awful design.

Regards,

Patrick
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
This would do:
SELECT t1.ID, t1.Name, t2.Name
FROM Parent_table t1 INNER JOIN 
(SELECT a1.ParentID, 
SUBSTRING((
SELECT a2.Name + ', ' 
FROM Child_table a2 
WHERE a1.ParentID = a2.ParentID
ORDER BY a2.Name
FOR XML PATH('')), 3, 1000)
FROM Child_table a1
GROUP BY a1.ParentID) t2 on t1.ID = t2.ParentID

Open in new window

Top Expert 2011
Commented:
select p.*
  from parent as P
  left outer join (select parentid,min(name) as name from child
                        group by parentid ) as c1
   on c1.parentid=p.id
  left outer join child as c2
  on c2.parentid=p.id
  and c2.name > c1.name
  left outer join child as c3
  on c3.parentid=p.id
  and c3.name > c2.name
  where ( c2.name is null or c2.name = (select min(name) from child as x
                                            where x.parentid=c1.parentid
                                                                     and x.name>c1.name)
            )
   and ( c3.name is null or c3.name = (select min(name) from child as x
                                            where x.parentid=c1.parentid
                                                                     and x.name>c2.name)
            )
ORDER BY C1.NAME,C2.NAME,C3.NAME,P.ID
Ephraim WangoyaSoftware Engineer

Author

Commented:
Both the solutions are pretty good, the only draw back with rrjegan17's solution is that its only applicable to MSSSQL Server2005 onwards where as lowfatspread's solution is applicable to most databases
Thanks for the great feed back

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial