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

Question

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

Answer 1

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