I have 2 table - parents and children. I want to return all parents sorted by their children's names. But since parents can have multiple children, I can't seem to figure out how to sort that. I would need to take the child that has alphabetically the first name (of each parent) and use it for sorting somehow.
Basically I would need to do these things
- Sort all the children of all parents and get the first child (alphabetically) of each.
For example for parent with id: 9 his child would be Alex Briggs (select * from children where parent_id = 9 order by asc LIMIT 1)
1. Alex Briggs (first one)
------------------------------
2. Ashley Briggs
3. Lucy Briggs
4. Lukas Briggs
And for parent with id: 3
1. Alex Skull (first one)
------------------------------
2. Don Skull
3. Erica Skull
And then return all parents sorted by their first child (alphabetically).
select * from parents order by ( their first child by alphabet .... ASC )
parents table
| id | fullname |
1 Mark Dever
2 John Witney
3 Joey Skull
4 Abraham Lincon
5 Donald Trump
6 Britney Huston
7 Martin Lu
8 Eric Tada
9 Andy Briggs
10 Linda Briggs
children table
| id | parent_id | fullname |
1 9 Lukas Briggs
2 9 Alex Briggs
3 10 Ashley Briggs
4 10 Lucy Briggs
5 1 Mark Driscoll
6 2 Zack Witney
7 2 Victoria Witney
8 3 Alex Skull
9 3 Don Skull
10 3 Erica Skull
result would be like this
{
parent_id: 9,
fullname: Andy Briggs,
children: [
...Alex, Ashley, Lucy...
]
},
{
parent_id: 10,
fullname: Linda Briggs,
children: [
...Alex, Ashley, Lucy...
]
},
{
parent_id: 3,
fullname: Joey Skull,
children: [
...Alex, Don, Erica...
]
},
{
parent_id: 1,
fullname: Mark Dever,
children: [
...Mark...
]
},
{
parent_id: 2,
fullname: John Witney,
children: [
...Victoria, Zack...
]
},