Cascading select until all Parents found in Table

Question

In my MySQL-Database i have one table called units with the structure like this:

ID, Name, parentUnitID, UnitTypeID, ...

Is it possible to generate a complete Unit-Chain with one query and one ID as the starting point? Cascading the query as long as there are more parents for the given ID? I can't find a way generating this without the depth limitation.

It is possible that the given ID has a parent which also itself has a parent, which also might have one, ...etc.

Edit:

The Result I am expecting is something like this:

IDDepth1, IDDepth2, IDDepth3, ..., NameDepth1, NameDepth2, NameDepth3, ...

Or

UnitTypeID1, Name1,  
UnitTypeID2, Name2,  
UnitTypeID3, Name3,  
...

To get the complete Unit-Chain for the given ID and parse it in PHP to build a cascading array.

Edit2:

I tried something like this

SELECT ID, Name
FROM Units as u1
WHERE UnitTypeID = "4"
AND EXISTS (
    SELECT ID, Name
    FROM Units as u2
    WHERE UnitTypeID = "5"
    AND u2.ParentUnitID = u1.ID
    AND EXISTS (
        SELECT ID
        FROM Units as u3
        WHERE ID = "1692820"
        AND u3.UnitTypeID = "6"
        AND u3.ParentUnitID = u2.ID
   )
);

But first it is static and second the result from EXISTS is not part of the SELECT itself.

By multiple parent i mean the parent can have a parent, can have a parent, can have a parent and so on. The Unit 7 has the Unit 6 as parent, 6 has 5 as parent, 5 has 4 as parent.. But i don't know how many parents one Unit in general has. — Thomson, Feb 13 '16 at 11:58
Your question is a duplicate of the one I mentioned. Just adapt for your problem. — Jorge Campos, Feb 13 '16 at 12:08

score 0 · Answer 1 · answered Feb 13 '16 at 12:37

You can achieve this by writing a stored procedure whitch will insert parents Id's into a temporary table and loop through the parents and so on.

I wrote the following procedure as a quick simple example; this is certainly not perfect but can be a beginning

DELIMITER $$

drop procedure if exists get_unitschain$$

CREATE PROCEDURE get_unitschain (IN childUnitID INTEGER)
BEGIN
    DROP TEMPORARY TABLE IF EXISTS tmp_chain; -- Will contain the parents ID's
    DROP TEMPORARY TABLE IF EXISTS tmp_processed; -- Copy of tmp_chain
    DROP TEMPORARY TABLE IF EXISTS tmp_parents; -- New parents identified by each WHILE loop

    CREATE TEMPORARY TABLE tmp_chain (
        ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
        UnitID INTEGER NOT NULL
    );

    CREATE TEMPORARY TABLE tmp_processed (
        UnitID INTEGER NOT NULL
    );

    CREATE TEMPORARY TABLE tmp_parents (
        UnitID INTEGER NOT NULL
    );

    INSERT INTO tmp_chain (UnitID) VALUES (childUnitId);
    INSERT INTO tmp_parents (UnitID) VALUES (childUnitId); -- To start the WHILE loop

    WHILE (SELECT COUNT(*) FROM tmp_parents) DO
        DELETE FROM tmp_parents;

        INSERT INTO tmp_parents (UnitID)
            SELECT units.parentUnitID 
                FROM tmp_chain
                    INNER JOIN units ON units.ID = tmp_chain.UnitId
                WHERE units.parentUnitID NOT IN (SELECT tmp_processed.UnitId FROM tmp_processed);

        INSERT INTO tmp_chain (UnitID) SELECT tmp_parents.UnitID FROM tmp_parents;
        INSERT INTO tmp_processed (UnitID) SELECT tmp_parents.UnitID FROM tmp_parents;
    END WHILE;

    SELECT units.UnitTypeID, units.Name FROM tmp_chain INNER JOIN units ON units.ID = tmp_chain.UnitID;

    DROP TEMPORARY TABLE tmp_chain;
    DROP TEMPORARY TABLE tmp_processed;
    DROP TEMPORARY TABLE tmp_parents;
END$$

To use the procedure :

CALL get_unitschain([child id]);

Cascading select until all Parents found in Table

1 Answers1