I have a table with the following structure:
+-----------+--------------+-----------+----------+
| member_id | household_id | firstName | lastName |
+-----------+--------------+-----------+----------+
| 14122 | 0 | Cynthia | Bookout |
+-----------+--------------+-----------+----------+
| 14123 | 0 | Kim | Caves |
+-----------+--------------+-----------+----------+
| 14124 | 14122 | Marvin | Bookout |
+-----------+--------------+-----------+----------+
| 14125 | 13974 | Stacey | Webb |
+-----------+--------------+-----------+----------+
| 14126 | 13974 | Nathan | Webb |
+-----------+--------------+-----------+----------+
| 14127 | 13974 | Adam | Webb |
+-----------+--------------+-----------+----------+
| 14128 | 13974 | Thomas | Webb |
+-----------+--------------+-----------+----------+
| 14129 | 0 | Missy | Hammock |
+-----------+--------------+-----------+----------+
| 14130 | 0 | Stephanie | Lewis |
+-----------+--------------+-----------+----------+
| 14131 | 0 | Kelly | Hutto |
+-----------+--------------+-----------+----------+
| 14132 | 14130 | James | Lewis |
+-----------+--------------+-----------+----------+
| 14133 | 0 | Cindy | Barnwell |
+-----------+--------------+-----------+----------+
| 14134 | 13828 | NATALIE | MCMILLAN |
+-----------+--------------+-----------+----------+
| 14135 | 13828 | Steven | Adams |
+-----------+--------------+-----------+----------+
| 14136 | 0 | Katherine | Gaskins |
+-----------+--------------+-----------+----------+
member_id and household_id have a parent-child relationship for certain records. Like for example record with member_id 14124 is the child of member_id 14122 (ie its household_id is the member_id of its parent).
The table actually contains thousands of records that are not in order. I want to display them as such that the parent followed by its child records and then the next parent and its child records appear and so on. The result need to be sorted by member_id too.
I have tried this:
SELECT * FROM members WHERE household_id IN (SELECT member_id FROM members WHERE household_id = 0) OR household_id = 0
ORDER BY member_id
But have no luck getting the desired results. Doing it programtically using PHP is taking too long since I have to iterate each record many times. Any help will be appreciated. Thanks in advanced.
Edit: This is my desired result:
+-----------+--------------+--------------+----------+
| member_id | household_id | firstName | lastName |
+-----------+--------------+--------------+----------+
| 14122 | 0 | Cynthia | Bookout |
+-----------+--------------+--------------+----------+
| 14124 | 14122 | Marvin Keith | Bookout |
+-----------+--------------+--------------+----------+
| 14123 | 0 | Kim | Caves |
+-----------+--------------+--------------+----------+
| 14125 | 13974 | Stacey | Webb |
+-----------+--------------+--------------+----------+
| 14126 | 13974 | Nathan | Webb |
+-----------+--------------+--------------+----------+
| 14127 | 13974 | Adam | Webb |
+-----------+--------------+--------------+----------+
| 14128 | 13974 | Thomas | Webb |
+-----------+--------------+--------------+----------+
| 14129 | 0 | Missy | Hammock |
+-----------+--------------+--------------+----------+
| 14130 | 0 | Stephanie | Lewis |
+-----------+--------------+--------------+----------+
| 14132 | 14130 | James | Lewis |
+-----------+--------------+--------------+----------+
| 14131 | 0 | Kelly | Hutto |
+-----------+--------------+--------------+----------+
| 14133 | 0 | Cindy | Barnwell |
+-----------+--------------+--------------+----------+
| 14134 | 13828 | NATALIE | MCMILLAN |
+-----------+--------------+--------------+----------+
| 14135 | 13828 | Steven | Adams |
+-----------+--------------+--------------+----------+
| 14136 | 0 | Katherine | Gaskins |
+-----------+--------------+--------------+----------+
Try the following query:
SELECT a.member_id as parent_id,
a.firstName as parent_firstName,
a.lastName as parent_lastName,
b.member_id as child_id,
b.firstName as child_firstName,
b.lastName as child_lastName
FROM members a inner join members b on a.member_id = b.household_id
ORDER BY a.member_id
