In table 1 I have records with a status.

In table 2 I have the parent child relationship

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:

CodePudding user response：

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