I have two tables in Oracle, Table1 looks something like this:
Id_1 | Id_1_Source
23872 | 23870
23873 | 23871
23874 | 23872
23875 | 23872
23876 | 23873
23877 | 23876
23878 | 23877
Table2 looks like this:
Source | Color
23870 | Yellow
23871 | Green
What I would like is to show the color of all the Id_1 in Table1, but
SELECT Table1.Id_1, Table2.Color
FROM Table1, Table2
WHERE Table1.Id_1_Source = Table2.Source
isn't good enough. The problem is that the Id_1_Source sometimes relates to the Source in Table2, but also sometimes to another Id_1 in table1. The other Id_1 in table1 can also be related to the Source in Table2, but also to yet another Id_1 in table1. Eventually all Id_1 id's can be traced back to an Id_1_Source which exists as Source in Table2, but I have no idea how I can get that output through sql.
What I would like as result is as follows:
Id_1 | Color
23872 | Yellow
23873 | Green
23874 | Yellow
23875 | Yellow
23876 | Green
23877 | Green
23878 | Green
I tried something with IF ... THEN or CASE, but I would have to do that multiple times and the amount of times needed could change over time, so I'm looking for another solution, perhaps make use of a LOOP somehow. Can someone please help?
As I understand your question, you can use a recursive query:
with cte (id_1, id_1_source, lvl) as (
select id_1, id_1_source, 1 lvl from table1
union all
select c.id_1, t1.id_1_source, c.lvl + 1
from table1 t1
inner join cte c on t1.id_1 = c.id_1_source
)
select c.id_1, t2.color
from (select c.*, row_number() over(partition by id_1 order by lvl desc) rn from cte c) c
inner join table2 t2 on t2.source = c.id_1_source
where c.rn = 1
The idea is to use the recursive query to identify the id_1_source of the "top" record in the hierarchy tree that is represented by table_1 for each id_1. You can then bring table_2 with a join.
ID_1 | COLOR ----: | :----- 23872 | Yellow 23873 | Green 23874 | Yellow 23875 | Yellow 23876 | Green 23877 | Green 23878 | Green