Getting all parent rows in one SQL query

Question

I have a simple MySQL table thats contains a list of categories, level is determined by parent_id:

id  name    parent_id
---------------------------
1   Home        0
2   About       1
3   Contact     1
4   Legal       2
5   Privacy     4
6   Products    1
7   Support     1

I'm attempting to make a breadcrumb trail. So i have the 'id' of the child, I want to get all available parents (iterating up the chain until we reach 0 "Home"). There could be any number or child rows going to an unlimited depth.

Currently I am using an SQL call for each parent, this is messy. Is there a way in SQL to do this all on one query?

Answer 1

AFAIK no.

This Sitepoint article may help you.

You could retrieve all the elements with one query, store it in an array and then iterate, as explained here and here

Answer 2

I think, there's no easy way to do that, using one query.

I would recommend to take a look at Nested Sets, that seems to fit your needs.

Answer 3

Adapted from here:

SELECT T2.id, T2.name
FROM (
    SELECT
        @r AS _id,
        (SELECT @r := parent_id FROM table1 WHERE id = _id) AS parent_id,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := 5, @l := 0) vars,
        table1 h
    WHERE @r <> 0) T1
JOIN table1 T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC

The line @r := 5 is the page number for the current page. The result is as follows:

1, 'Home'
2, 'About'
4, 'Legal'
5, 'Privacy'