Child ID Parent ID concept

Question

The source tables are

TABLE 1     
ID  NAME    
X1  ssnnmm  

TABLE 2     
ID  NAME    LEVEL
Z1  ss      1
Z2  nn      2
Z3  mm      3

I want the result as below

RESULT      
CHILD_ID    PARENT_ID   
X1          Z1  
Z1          Z2  
Z2          Z3  
Z3          NULL

which is based on level column of TABLE 2

Thanks in advance.

You tagged 3 different RDBMS here, I've removed all of them as they all operate very differently. Which DBMS are you ***really*** using? Update (edit) your question and tag **only** that DBMS. Don't forget, when you update your question, to include what you already tried as well; Stack Overflow isn't a free coding service it's a Q&A website to help users with problems they've tried to solve themselves and have been unable to do so. — Thom A, Aug 20 '18 at 09:02
I want the child_id and parent_id as mentioned in result table — E.Manju, Aug 20 '18 at 09:07
Then please tag it and don't tag SQL server and MySQL, which are completely different products. — Thom A, Aug 20 '18 at 09:20
Can you clarify how exactly the columns in the two tables relate to each other so we don't have to guess? E.g. Why is it that Z3 doesn't have a parent while Z1 and Z2 do? — 404, Aug 20 '18 at 09:23
depending on column LEVEL of TABLE 2 since z3 doesn't have any hierarchy level its parent_id is NULL — E.Manju, Aug 20 '18 at 09:27
see depending on level parent_id will be taken and child_id will be taken subsequently — E.Manju, Aug 20 '18 at 09:35

score 1 · Answer 1 · answered Aug 20 '18 at 11:05

This returns the results that you specify:

with t1 as (
      select 'X1'::text as id, 'ssnnmm' as name
     ),
     t2 as (
      select 'Z1'::text as id, 'ss' as name, 1::int as level union all
      select 'Z2' as id, 'nn' as name, 2::int as level union all
      select 'Z3' as id, 'mm' as name, 3::int as level
     )
select t2.id, t2p.id
from t2 left join
     t2 t2p
     on t2p.level = t2.level + 1
union all
select t1.id, t2.id
from t1  join
     t2
     on t2.level = 1;

Child ID Parent ID concept

1 Answers1