MySQL从桌面上选择父母和孩子

Question

I have one table that holds parent pages and sub pages, where top page is parent_id = 0 and subpage parent_id is the id of as other page.

ID 1 | parent_id 0
ID 2 | parent_id 0
ID 3 | Parent_id 1
ID 4 | parent_id 1
ID 5 | parent_id 0

So I have

SELECT *
FROM pages AS parent
WHERE parent.parent_id = 0

SELECT *
FROM pages AS sub
WHERE sub.parent_id != 0

The above separate query's work. But i'm sure there is a may to list all pages with one query.

I'm trying to get a list similar to this, grouping sub pages to the parent page

Main (Title1)
Main (Title2)
     SUB (Title1)
     SUB (Tible2)
Main (Title3)
etc

I've been trying my luck with inner join and left join but I can't solve this.

I has hoping a kind soul with some experience will give me a hand. :)

Thanks

Answer 1

Try following query, It has additional column page_type which indicates page type.

SELECT page.* , IF( (SELECT p.parent_id FROM pages p WHERE p.id = page.id) = 0, "PARENT", "CHILD" ) AS PAGE_TYPE FROM pages AS page