How can I create a query that shows children of an item recursively

Question

I have a MySQL table which has the following format:

CREATE  TABLE IF NOT EXISTS `Company` (
  `CompanyId` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `Name` VARCHAR(45) NULL ,
  `Address` VARCHAR(45) NULL ,
  `ParentCompanyId` INT UNSIGNED NULL ,
  PRIMARY KEY (`CompanyId`) ,
  INDEX `fk_Company_Company_idx` (`ParentCompanyId` ASC) ,
  CONSTRAINT `fk_Company_Company`
    FOREIGN KEY (`ParentCompanyId` )
    REFERENCES `Company` (`CompanyId` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

So to clarify, I have companies which can have a parent company. This could result in the following example table contents:

CompanyId    Name    Address        ParentCompanyId
1            Foo     Somestreet 3   NULL
2            Bar     Somelane 4     1
3            McD     Someway 1337   1
4            KFC     Somewhere 12   2
5            Pub     Someplace 2    4

Now comes my question. I want to retrieve all children of CompanyId 2 recursive. So the following result set should appear:

CompanyId    Name    Address        ParentCompanyId
4            KFC     Somewhere 12   2
5            Pub     Someplace 2    4

I thought of using the With ... AS ... statement, but it is not supported by MySQL. Another solution I thought of was using a procedure or function which returns a result set and union it with the recursive call of that function. But MySQL does only support column types as return values.

The last possible solution I thought about was to create a table with two fields: CompanyId and HasChildId. I could then write a procedure that loops recursively through the companies and fills the table with all recursive children by a companyid. In this case I could write a query which joins this table:

SELECT CompanyId, Name, Address
FROM   Company C -- The child
INNER JOIN CompanyChildMappingTable M
        ON M.CompanyId = C.HasChildId
INNER JOIN Company P -- The parent
        ON P.CompanyId = M.CompanyId
WHERE P.CompanyId = 2;

This option should be a fast one if i'd call the procedure every 24 hours and fill the table on the fly when new records are inserted into Company. But this could be very tricky and I should do this by writing triggers on the Company table.

I would like to hear your advice.

Solution: I've built the following procedure to fill my table (now it just returns the SELECT result).

DELIMITER $$
DROP PROCEDURE IF EXISTS CompanyFillWithSubCompaniesByCompanyId$$

CREATE PROCEDURE CompanyFillWithSubCompaniesByCompanyId(IN V_CompanyId BIGINT UNSIGNED, IN V_TableName VARCHAR(100))
BEGIN

    DECLARE V_CONCAT_IDS VARCHAR(9999) DEFAULT '';
    DECLARE V_CURRENT_CONCAT VARCHAR(9999) DEFAULT '';

    SET V_CONCAT_IDS = (SELECT GROUP_CONCAT(CompanyId) FROM Company WHERE V_CompanyId IS NULL OR ParentCompanyId = V_CompanyId);
    SET V_CURRENT_CONCAT = V_CONCAT_IDS;

    IF V_CompanyId IS NOT NULL THEN

        companyLoop: LOOP

            IF V_CURRENT_CONCAT IS NULL THEN
                LEAVE companyLoop;
            END IF;

            SET V_CURRENT_CONCAT = (SELECT GROUP_CONCAT(CompanyId) FROM Company WHERE FIND_IN_SET(ParentCompanyId, V_CURRENT_CONCAT));
            SET V_CONCAT_IDS = CONCAT_WS(',', V_CONCAT_IDS, V_CURRENT_CONCAT);

        END LOOP;

    END IF;

    SELECT * FROM Company WHERE FIND_IN_SET(CompanyId, V_CONCAT_IDS);

END$$

You _could_ do it with _N_ self joins for _N_ number of hierarchy levels but that's going to be _hardcoded_ and too cumbersome — Ejaz, Apr 19 '13 at 15:07

score 0 · Accepted Answer · edited May 23 '17 at 12:18

0

Refer:

Recursive MySQL Query with relational innoDB

AND

How to find all child rows in MySQL?

It shall give a idea of how such a data structure, can be dealt in MYSQL

edited May 23 '17 at 12:18

Community

1
1

answered Apr 19 '13 at 14:53

Akash

4,956
11
42
70

I have chosen to create a temp table with engine MEMORY and fill it by a stored procedure after creation. This fits most by my current application architecture. Thanks for all your help. – Arnold Pistorius Apr 22 '13 at 10:12
I have added the stored procedure above. – Arnold Pistorius Apr 22 '13 at 12:48

score 0 · Answer 2 · answered Apr 19 '13 at 15:03

One quickest way to search is, use company id values in power of 2. companyId = parentId * 2 then query database like, select * from company where ((CompanyId % $parentId) == 0 ) I tried this code, it's quick but problem is it creates child's id as parentId * 2 and if depth of child goes deep, int, float may go out of range. So, I re-created my whole program.

How can I create a query that shows children of an item recursively

2 Answers2