I am having an issue with multi level pages query using mysql.
My table structure is like this. The table name is 'default_pages'
id title parent_id
18 About Us 0
19 ab 1 18
20 contact 0
21 ab 2 18
22 sub 1 21
23 sub 2 22
My query is
SELECT p.id AS id, p.title AS ptitle, parent.id AS parent, grandparent.id AS grandparent
FROM default_pages AS p
LEFT JOIN default_pages AS parent ON p.parent_id = parent.id
LEFT JOIN default_pages AS grandparent ON parent.parent_id = grandparent.id
where p.parent_id = 18
But I am getting the below output
id ptitle parent grandparent
19 ab 1 18 null
21 ab 2 18 null
Desired output should be
id ptitle parent grandparent
19 ab 1 18 null
21 ab 2 18 null
22 sub 1 21 18
23 sub 2 22 21
Unfortunately, MySQL doesn't have direct support for hierarchical or recursive queries, so you have to approach this using a fixed number of multiple joins (or using a stored procedure). I think you want to check for 18 in all the parent ids:
SELECT p.id AS id, p.title AS ptitle, parent.id AS parent, grandparent.id AS grandparent
FROM default_pages p LEFT JOIN
default_pages parent
ON p.parent_id = parent.id LEFT JOIN
default_pages grandparent
ON parent.parent_id = grandparent.id
WHERE 18 in (p.parent_id, parent.parent_id, grandparent.parent_id);
The error is in your where clause, it limits the results to all rows where parent_id = 18. Instead, it seems you want all rows which have a parent. This should work:
SELECT p.id AS id, p.title AS ptitle, parent.id AS parent, grandparent.id AS grandparent
FROM default_pages AS p
LEFT JOIN default_pages AS parent ON p.parent_id = parent.id
LEFT JOIN default_pages AS grandparent ON parent.parent_id = grandparent.id
where p.parent_id != 0