Build tree from database

Question

I have two tables. Table 1. (tbl_1)

| ID | Name         |
| -- | -------------|
| 1  |   Company1   |
| -- | -------------|
| 2  |   Company2   |
| -- | -------------|
| 3  |   Company2   |

Table 2. (tbl_2)

| ID |  Company_group |
| -- |  ------------- |
| 1  |  Company2      |
| -- |  ------------- |
| 2  |  Company2      |
| -- |  ------------- |
| 3  |  Company2      |

I now that Company_2 is parent company an i want to get next result.

| ID | Name         | RootName  | RootId |
| -- | -------------| --------- | ------ |
| 1  |  Company1    |  Company2 |    2   |
| -- | -------------| ----------|--------|
| 3  |  Company3    | Company2  |    2   |

I don't know parentId. But i can select all parent companies with follow query:

SELECT DISTINCT id parentId,
 name parent_name FROM tbl_1 WHERE name in (
SELECT DISTINCT
Company_group
FROM tbl_2)

How can i build tree for this hierarchy? I can not think, please help.

It is a strange architecture for this case but architect of database is not me.

Also i wrote query but it works not correct. It returns more records.

SELECT ac.id_c parentId, acc.id, ac.Company_group parent_name
FROM tbl_2 ac
JOIN tbl_2 acc
ON ac.Company_group = acc.Company_group
AND ac.id in (
SELECT DISTINCT id parentId
 FROM tbl_1 WHERE name in (
SELECT DISTINCT
id parentId
FROM tbl_2)
)
WHERE ac.Company_group iS NOT NULL AND acc.id IS NOT NULL
and ac.id <> acc.id

ORDER BY ac.Company_group

David דודו Markovitz · Accepted Answer · 2016-10-20 16:18:33Z

1

create table tbl_1 (ID int,Name varchar(100));
insert into tbl_1 (ID,Name) values (1,'Company1'),(2,'Company2'),(3,'Company3');

create table tbl_2 (ID int,Company_group varchar(100));
insert into tbl_2 (ID,Company_group) values (1,'Company2'),(2,'Company2'),(3,'Company2');

select      t1.ID
           ,t1.Name
           ,t2.Company_group    as RootName
           ,t1_b.ID             as RootId

from                    tbl_1   t1

            join        tbl_2   t2

            on          t2.ID   =
                        t1.ID

            join        tbl_1   t1_b

            on          t1_b.Name   =
                        t2.Company_group

where       t1.ID <> t1_b.ID
;

edited Oct 20, 2016 at 16:18

answered Oct 20, 2016 at 14:20

David דודו Markovitz

45.2k7 gold badges75 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user6408649 Over a year ago

Thank you. Your query also returns more records than i wait. But result less than returns in my last query.

David דודו Markovitz Over a year ago

@Seva, you are welcome. this query returns the right results for your sample. If you are getting undesired results in production then please change the sample so it would represent correctly the production data.

user586399 · Accepted Answer · 2016-10-20 13:12:31Z

0

You simply need to join the table with itself:

SELECT *
FROM Company c1
   LEFT OUTER JOIN Company c2 ON c1.ParnetID = c2.ID

answered Oct 20, 2016 at 13:12

user586399

1 Comment

user6408649 Over a year ago

Hi. You don't understand me. I have not ParnetID in database. I have only Company_group and it is varchar. See how I get parent company.

Collectives™ on Stack Overflow

Build tree from database

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related