Mysql - How to select all parents of a list of children?

Question

I have a table where each record contains his father and this hierarchy may have up to 6 levels. The query below search all parents of the department named "DEPARTMENT ABCD". This query works perfectly.

SELECT D2.id, D2.name, D2.id_parent
FROM (
    SELECT
        @d AS _id,
        (SELECT @d := id_parent FROM department WHERE id = _id) AS id_parent,
        @l := @l + 1 AS level
    FROM
        (SELECT 
            @d := (select id from department where name = 'DEPARTMENT ABCD'), 
            @l := 0
        ) initial_level,
        department D
    WHERE @d <> 0
) D1
JOIN department D2 ON D1._id = D2.id
ORDER BY D1.level DESC;

However, if I want to get the parents of more than one child at the same time (using like in the query below), I get the error: "Error Code: 1242. Subquery returns more than 1 row".

Query with the error "Subquery returns more than 1 row":

SELECT D2.id, D2.name, D2.id_parent
FROM (
    SELECT
        @d AS _id,
        (SELECT @d := id_parent FROM department WHERE id = _id) AS id_parent,
        @l := @l + 1 AS level
    FROM
        (SELECT 
            @d := (select id from department where name like 'DEPARTMENT %A%'), 
            @l := 0
        ) initial_level,
        department D
    WHERE @d <> 0
) D1
JOIN department D2 ON D1._id = D2.id
ORDER BY D1.level DESC;

How could I do to get the parents of more than one child at the same time?

I added a sample in SQL Fiddle: http://sqlfiddle.com/#!9/f182fb/3

If I got you, you want to display a hierarchical structure of items. If so, you could consider to choose a recursive approach as described here http://stackoverflow.com/a/20216006/6435375 — Rico Herlt, Jun 07 '16 at 16:11
Thanks for the answer. I saw the link informed and the approaches described show the levels in the form of columns and also not support seek the parents of more than one child. — David, Jun 07 '16 at 16:35
Would you create a small fiddle sample of your data, or even a fake data so we can test the results please?. (http://sqlfiddle.com) — Jorge Campos, Jun 07 '16 at 16:39
Hi Jorge. I created a small fiddle sample (http://sqlfiddle.com/#!9/f182fb/3). If you use the queries above will be able to simulate this situation. Thank you in advance! — David, Jun 07 '16 at 17:14
that specific error message means you're running a query which can return only ONE row of data, e.g. your `@d := (select id ...)`. since you're doing an assignment, only one value can be assigned, not a SET of values. — Marc B, Jun 07 '16 at 19:30
Yes correct. My doubt was actually how to change my query to contemplate several children. — David, Jun 07 '16 at 21:57

score 2 · Accepted Answer · answered Jun 07 '16 at 19:23

Given that the hierarchy may have up to 6 levels, I would suggest to join the table 6 times, to get all the ancestors of the selected departments:

select     distinct id, name, id_parent
from       (
            select      d6.id_parent as id6,
                        d5.id_parent as id5,
                        d4.id_parent as id4,
                        d3.id_parent as id3,
                        d2.id_parent as id2,
                        d1.id_parent as id1,
                        d1.id        as id0
            from        department d1
            left join   department d2 on d2.id = d1.id_parent 
            left join   department d3 on d3.id = d2.id_parent 
            left join   department d4 on d4.id = d3.id_parent  
            left join   department d5 on d5.id = d4.id_parent  
            left join   department d6 on d6.id = d5.id_parent
            where       d1.name like 'DEPARTMENT A%'
           ) as h
inner join department d on d.id in (id0, id1, id2, id3, id4, id5, id6)
order by   1;

SQL fiddle

Perfect, thank you even! It worked perfectly! My only concern is whether the query can be a little slow with the joins with thousands of records. You think you can bring a performance problem or it anyway will work well ? Thank you in advance! — David, Jun 07 '16 at 21:57
I don't think performance will be an issue. I actually think the correlated sub query in your original SQL might be more of a performance drain ( `(SELECT @d := id_parent FROM department WHERE id = _id)` ). But that is just my intuition, I could be wrong. — trincot, Jun 08 '16 at 07:29
Okay, I got it. Thank you so much for all your help and for the clarification!! =] — David, Jun 08 '16 at 14:31

Mysql - How to select all parents of a list of children?

1 Answers1