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 |
+-----------+--------------+--------------+----------+