Parent Child stations in a company hierarchically

Question

Hence the parent company should have access to all its children companies' stations, hierarchically. For example, we have 3 companies A, B, and C owning respectively 10, 5, and 2 charging stations. Company B belongs to A and company C belongs to B. Then we can say that company A owns 17, company B owns 7, and company C owns 2 charging stations in total.

Database

Station (id, name, company_id, address)

Company (id, parent_company_id, name)

What is **precise** MySQL version? – Akina Nov 11 '21 at 06:53 — Akina, Nov 11 '21 at 06:53
@Akina MySQL Version 8.0.17 – Huzoor Bux Nov 11 '21 at 07:50 — Huzoor Bux, Nov 11 '21 at 07:50
In your version you can use recursive CTE. – Akina Nov 11 '21 at 08:14 — Akina, Nov 11 '21 at 08:14

score 0 · Answer 1 · answered Nov 11 '21 at 06:42

This is how i have created query got help from this.

SELECT id,name,parent_company_id FROM
    (SELECT id,name,parent_company_id,
            CASE WHEN id = 1 THEN @idlist := CONCAT(id)
                 WHEN FIND_IN_SET(parent_company_id,@idlist) THEN @idlist := CONCAT(@idlist,',',id)
            END as checkId
     FROM Test
     ORDER BY id ASC) as T
WHERE checkId IS NOT NULL

It will return all ids and on those ids base i can get stations count.

Parent Child stations in a company hierarchically

1 Answers1