SQL Status of Parent with Parent-Child relationship in other table

user_18976605 0
Viewed 16

In table 1 I have records with a status.

key Status Combi Parent
key1 ABCD 1 0
key2 ABCD 1 0
key3 XYZ 1 0
key4 XYZ 1 1
key5 QWERT 1 1
key6 ABCD 0 0

In table 2 I have the parent child relationship

key Parentkey Check
key1 key4 A
key2 key4 B
key3 key5 A
key4 key4 B
key5 key5 A

The Result I'm looking for is a table with the ParentStatus. But only when Table1.Combi = 1 and Table1.Parent = 0 and Table2.Check = A. Like this:

key ParentStatus
key1 XYZ
key2
key3 QWERT
key4
key5
key6
sql
1 Answers

We can use the conditions such as check_ = 'A' in the join condition. If we use where then the lines will not be returned.

create table table1 (key_ varchar(10), status varchar(10), combi int, parent int);
insert into table1 values
('key1','ABCD',1,0),
('key2','ABCD',1,0),
('key3','XYZ',1,0),
('key4','XYZ',1,1),
('key5','QWERT',1,1),
('key6','ABCD',0,0);

create table table2(key_ varchar(10), ParentKey varchar(10), Check_ char);
insert into table2 values
('key1','key4','A'),
('key2','key4','B'),
('key3','key5','A'),
('key4','key4','B'),
('key5','key5','A');

select 
  t.key_,
  ttt.status Parent_Status
from table1 t 
left join table2 tt 
on t.key_ = tt.Key_ and Check_ = 'A' and parent = 0
left join table1 ttt
on tt.ParentKey = ttt.key_ and t.combi = 1

key_ | parent_status
:--- | :------------
key1 | XYZ          
key2 | null         
key3 | QWERT        
key4 | null         
key5 | null         
key6 | null

db<>fiddle here

Admin1
Related