MySQL Select All Rows Linked By ID In Same Table

Question

I have a table with the fields:

Parent references the primary key ID in the same table (not foreign key).

+----+--------+------------+
| ID | Parent |   Title    |
+----+--------+------------+
|  1 |      0 | Wallpapers |
|  2 |      1 | Nature     |
|  3 |      2 | Trees      |
|  4 |      3 | Leaves     |
+----+--------+------------+

I'm trying to select a row and all its parents. For example if I select "Leaves" I want to get "Trees", "Nature", and "Wallpaper" as well since that is the parent path up the hierarchy. Is this possible in one query? At the moment I'm using a loop in ColdFusion to get the next parent each iteration but it's not efficient enough.

Nature should return Wallpapers since Nature's parent is 1 and 1 is Wallpapers. It shouldn't go forward and find all parents of that one record. This is being used for photo albums so think of it as recursive albums inside each other, so if I'm inside album "Nature" the parent album is Wallpapers etc. — J. Kingsley, Sep 04 '15 at 12:16
Why does `Leaves` return `Nature` and `Trees`? The parent of `Leaves` is `1`, and that's `Wallpapers`. — Barmar, Sep 04 '15 at 12:25
http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ — Barmar, Sep 04 '15 at 12:25
Use a `UNION` to combine the query that returns the main row with a query that returns its parent. — Barmar, Sep 04 '15 at 12:26
check [Link](https://bikashshaw.wordpress.com/2014/02/10/employee-id-employee-name-and-manager-id-self-join-sql-query-as-interview-question/) — Satender K, Sep 04 '15 at 12:28
Sorry my mistake, you're right leaves should return trees. I've updated it now. — J. Kingsley, Sep 04 '15 at 12:28
Since MySQL doesn't support CTE's, you're basically left with this: http://stackoverflow.com/questions/5291054/generating-depth-based-tree-from-hierarchical-data-in-mysql-no-ctes/5291159#5291159 — Mihai Ovidiu Drăgoi, Sep 04 '15 at 12:36

score 1 · Answer 1 · edited Dec 20 '18 at 10:10

1

You have to use a self join to achieve this:-

 SELECT T1.Title AS CHILD, T2.Title AS PARENT
 FROM YOUR_TABLE T1, YOUR_TABLE T2
 WHERE T1.PARENT = T2.ID
 AND T1.Title = 'Leaves'

edited Dec 20 '18 at 10:10

Simon Jakobi

117
6

answered Sep 04 '15 at 13:18

Ankit Bajpai

13,128
4
25
40

Meloman · Answer 2 · 2020-08-25T12:26:31.877

In PHP, I wrote a function that retrieve all the hierarchy of my route table (infinite depth).

I think that, in this example, you can see SQL syntax of query you are looking for. I use same column names except in lowercase.

function get_breadcrumb($route_id) {
    
    //access PDO mysql object instance
    global $db;

    // sanitize route ID
    $route_id = intval($route_id);
    
    // query construction
    $q = "SELECT T2.*
            FROM (
                SELECT
                    @r AS parent_id,
                    (SELECT @r := `parent` FROM `route` WHERE id = parent_id) AS `parent`,
                    @l := @l + 1 AS `depth`
                FROM
                    (SELECT @r := $route_id, @l := 0) vars,
                    `route` T3
                WHERE @r <> 0) T1
            JOIN `route` T2
                ON T1.parent_id = T2.id
            ORDER BY T1.`depth` DESC";
    
    // call query in database
    if($res = $db->query($q)) {
        if($res = $res->fetchAll(PDO::FETCH_ASSOC)) {
            if($size = sizeof($res) > 0) { 
                
                // push results in breadcrumb items array
                $breadcrumb['items']  = $res;
                $breadcrumb['levels'] = $size;
                
                // retrieve only titles
                $titles = array_column($res, 'title');

                // construct html result seperated by '>' or '/'
                $breadcrumb['html']['gt'] = implode(' > ', $titles);
                $breadcrumb['html']['sl'] = implode(' / ', $titles);

                // returns all result (added in SESSION too)
                return $_SESSION['app']['breadcrumb'] = $breadcrumb;
            }
        }
    }
    
    // no result for that route ID
    return false;
}

Satender K · Answer 3 · 2015-09-04T12:51:54.643

-1

Try this

SELECT 
      DISTINCT c.id AS 'ID', c.title AS 'Name'
FROM YourTable c, YourTable p 
WHERE c.id = p.parent AND c.title = 'Leaves'

edited Sep 04 '15 at 12:51

answered Sep 04 '15 at 12:29

Satender K

571
3
13

score -1 · Answer 4 · answered Sep 04 '15 at 12:30

-1

Use a UNION to combine the query that returns the main row with another query that returns the parent row.

SELECT *
FROM YourTable
WHERE Title = "Leaves"
UNION
SELECT t1.*
FROM YourTable AS t1
JOIN YourTable AS t2 ON t1.id = t2.parent
WHERE t2.Title = "Leaves")

answered Sep 04 '15 at 12:30

Barmar

741,623
53
500
612

This only returns one parent, not all parents of a record. – J. Kingsley Sep 04 '15 at 12:34
I thought that's what you want: "It shouldn't go forward and find all parents of that one record". – Barmar Sep 04 '15 at 12:38
You want the parent, the grandparent, the great-grandparent, etc.? – Barmar Sep 04 '15 at 12:39
By that I mean if I selected something like Trees I don't want it to get Leaves, just the stuff before it if that makes sense. – J. Kingsley Sep 04 '15 at 12:39
And you said "you're right, leaves should return trees". – Barmar Sep 04 '15 at 12:39
Sorry having read what I've put I've gotten myself confused. Leaves should return trees, nature and wallpapers as they are all parents of each other. – J. Kingsley Sep 04 '15 at 12:43
MySQL can't do recursion like this in SQL, you'll need to use a procedure, either in the calling language or a MySQL Stored Procedure. I'm trying to find similar questions, but I keep finding questions about getting all the children, not getting the parents. – Barmar Sep 04 '15 at 12:48
This is actually easier than finding all the children, because there's only one parent but there can be many children. So it's just one query per level until you reach the root, you don't need to loop at each level. – Barmar Sep 04 '15 at 12:48

MySQL Select All Rows Linked By ID In Same Table

4 Answers4

Linked