Select Rows From First Table, For Same Column Values in Second Table-CodePudding

My requirement is as follows: for each Deal Id, I want to check its corresponding records in the Deal Interest table, and if any one for the row contains Y for the UserModified column, I do not want to select that Deal Id.

So at the end output should contain these Deal Ids:

1001 - NO - As One of the record in Deal Interest Table is Y
1002 - YES - As all entries are N
1003 - YES - As all entries are N

Table structures are as follows:

Deal table:

 ------- ------ --------- 
|Deal_ID|Status|InputMode|
 ------- ------ --------- 
|1001   |V     |C        |
 ------- ------ --------- 
|1002   |V     |C        |
 ------- ------ --------- 
|1003   |V     |C        |
 ------- ------ ---------

Deal Interest table:

 ------ ------------ ----------- 
|ARIDNR|UserModified|PaymentDate|
 ------ ------------ ----------- 
|1001  |N           |2021-10-12 |
 ------ ------------ ----------- 
|1001  |N           |2021-11-12 |
 ------ ------------ ----------- 
|1001  |Y           |2021-12-13 |
 ------ ------------ ----------- 
|1001  |N           |2022-01-12 |
 ------ ------------ ----------- 
|1002  |N           |2021-11-24 |
 ------ ------------ ----------- 
|1002  |N           |2021-12-23 |
 ------ ------------ ----------- 
|1003  |N           |2021-11-14 |
 ------ ------------ ----------- 
|1003  |N           |2022-01-11 |
 ------ ------------ -----------

CodePudding user response：

I guess you want to know all ID's that have no row in DealInterestTable with value Y in column UserModified ?
If that is correct, then this query might help you

select d.Deal_ID
from   Deal d
where  not exists (select 1 from DealInterestTable it 
                   where it.ARIDNR = d.Deal_ID 
                   and it.UserModfied = 'Y')