How to order by a related table that has many entries for one item?

0
votes

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

  1. 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...
    ]
},
1
phpmysqlsql

1 Answers

1
votes

Since you want to order the children name,you can use the below query.

SELECT b.parent_id,a.fullname, GROUP_CONCAT(b.fullname ORDER BY b.fullname ASC) children
FROM parent a
JOIN children b
    ON a.id=b.parent_id
GROUP BY b.parent_id,a.fullname;