How to select parents and children?

[adrianle]

..and I mean that in a literal sense!  I have a mySQL table with Parent information in it.  I also have a table with Child information in it, each with a unique ID related to one of the records in the Parent table.  Some Parents have multiple Children.

 

I need to run a select statement that will return ALL family records, ideally with the parent first, and then their children, and so on. Suggestions?  I posted this question on a mysql forum and got no replies.  

Edited September 18, 2015 by adrianle

[scootstah]

Can children also be parents? If so, you'll want a tree structure. I prefer the nested set model myself.

[Barand]

Sounds like this is the model that are describing

+--------------+
|  parent      |
+--------------+
|  parent_id   |-----+
|  parentname  |     |
+--------------+     |        +-------------+
                     |        |    child    |
                     |        +-------------+
                     |        |  child_id   |
                     |        |  childname  |
                     +-------<|  parent_id  |
                              +-------------+

in which case JOIN using the parent id column

SELECT p.parent_id
, p.parentname
, c.childname
FROM parent p
    LEFT JOIN child c ON p.parent_id = c.parent_id
ORDER BY parentname

Loop through the results outputting the parent name only when it changes to new value.