Need to run multiple joins where only matching rows will be returned. I would like to get values only from matching rows

I have such query:

select dc.STATUSWORK "DC STATUS", de.STATUSWORK "DE STATUS", r.STATUSWORK "R STATUS", g.STATUSWORK "G STATUS", c.STATUSWORK "CONTRACT Status", c.PYID 
from CONTRACT c
join VDC dc on (dc.COVERKEY = c.KEY)
join VDE de on (de.COVERKEY = c.KEY)
join RCK r on (r.COVERKEY = c.KEY)
join GDE g on (g.COVERKEY = c.KEY)
join CDOC c on (c.COVERKEY = c.KEY)
where c.PYID = 'C-1000';

For sure if not all the join conditions together are matching, no rows will be returned, if I use some left or right join, also rows with NULL values will be returned

How can I run the query to check before what are the matching rows and feed a new query to return only the matching tables/rows?

2 Answers

0
Jen R On Best Solutions

If I understand what you want correctly, this should do it. This will return all rows from Contract as long as they have a match in at least one of the other rows. Switch out the ORs for ANDs if you need all tables to have a match.

select dc.STATUSWORK "DC STATUS"
   , de.STATUSWORK "DE STATUS"
   , r.STATUSWORK "R STATUS"
   , g.STATUSWORK "G STATUS"
   , c.STATUSWORK "CONTRACT Status", c.PYID 
from CONTRACT c
   left join VDC dc on (dc.COVERKEY = c.KEY)
   left join VDE de on (de.COVERKEY = c.KEY)
   left join RCK r on (r.COVERKEY = c.KEY)
   left join GDE g on (g.COVERKEY = c.KEY)
   left join CDOC c on (c.COVERKEY = c.KEY)
where c.PYID = 'C-1000'
and (
       exists (Select 1 from VDE where COVERKEY = c.KEY)
       or
       exists (Select 1 from VDC where COVERKEY = c.KEY)
       or
       exists (Select 1 from RCK where COVERKEY = c.KEY)
       or
       exists (Select 1 from GDE where COVERKEY = c.KEY)
       or
       exists (Select 1 from CDOC where COVERKEY = c.KEY)
);
0
Dugggie On

I've mocked up a few tables and a query to see if this is what you need.

create table CONTRACT(ConKey int, ConName varchar(20));

insert into CONTRACT(ConKey, ConName) Values(1, 'AAA');
insert into CONTRACT(ConKey, ConName) Values(2, 'BBB');
insert into CONTRACT(ConKey, ConName) Values(3, 'CCC');

create table VDC(VDCKey int, StatusWork varchar(20));  

insert into VDC(VDCKey, StatusWork) Values(1, 'Status 1');
insert into VDC(VDCKey, StatusWork) Values(3, 'Status 2');
insert into VDC(VDCKey, StatusWork) Values(4, 'Status 3');  

create table VDE(VDEKey int, StatusWork varchar(20));  

insert into VDE(VDEKey, StatusWork) Values(1, 'Status 4');
insert into VDE(VDEKey, StatusWork) Values(3, 'Status 5');
insert into VDE(VDEKey, StatusWork) Values(4, 'Status 6');  

create table RCK(RCKKey int, StatusWork varchar(20));  

insert into RCK(RCKKey, StatusWork) Values(5, 'Status 7');
insert into RCK(RCKKey, StatusWork) Values(2, 'Status 8');
insert into RCK(RCKKey, StatusWork) Values(1, 'Status 9');  

Here is the query :-

Select c.ConKey, c.ConName, dc.StatusWork, de.StatusWork, r.StatusWork 
from CONTRACT c
LEFT JOIN VDC dc ON dc.VDCKey = c.ConKey
LEFT JOIN VDE de on de.VDEKey = c.ConKey
LEFT JOIN RCK r on r.RCKKey = c.ConKey;

This is the result :-

ConKey  ConName StatusWork  StatusWork  StatusWork
1       AAA     Status 1    Status 4    Status 9
2       BBB                             Status 8
3       CCC     Status 2    Status 5