Seva Seva - 1 month ago 17
SQL Question

Build tree from database

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

Answer
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
;