Need help with Join Problem

Question

Hi

I have 2 tables

Table 1

ID  Status
1   D
2   F
3   D


Table 2

SID ID  Approve
1   1   N
2   1   Y
3   1   Y
4   2   Y
5   3   Y

Result:-  Should be 
Table 1 (ID, Status)
3, D
2,F

Should not display 1 (As one of child rows have N in the Approve Column)

I need a query to joins 2 tables on ID and finds records that don not have N in their Approve column. Does anyone have any clue how to implement this?

I tried

SELECT * FROM Table1 AS t1 
INNER JOIN Table2 AS t2 
ON t2.id = t1.id 
WHERE t2.Approve != 'N' 

Doesn’t work

Answer 1

SELECT *
FROM Table1 t1
WHERE NOT EXISTS(SELECT * FROM Table2 t2 WHERE t2.ID = t1.ID AND t2.Approve = 'N')

Answer 2

Please Try

SELECT distinct t1.* 
FROM Table1 AS t1 
INNER JOIN Table2 AS tyes
ON tyes.id = t1.id
AND tyes.approve ='Y'
LEFT OUTER JOIN Table2 as tno
ON tno.id = t1.id
AND tno.approve ='N'
where tno.sid is null

It will select any line which is explicitely approved and never disapproved

Answer 3

Select * from table1 where id not in (select id from table2 where approve = 'N')

Answer 4

hi,

ID 1 is still returned since there is also a record in table 2 where approve = 'Y' for ID 1. If you want to exclude any ID where approve is 'N' for any SID then you'll have to work with subqueries; roughly:

SELECT t1.ID,T1.Status FROM Table1 AS t1 INNER JOIN Table2 AS t2 ON t2.id = t1.id
where t1.id NOT IN (select id from Table2 where approve = 'N' and id = t1.id)

regards,
Stijn

Answer 5

More efficient and possibly easier to read is the following:

SELECT * FROM Table1 AS t1 
LEFT JOIN Table2 AS t2 
ON t2.id = t1.id 
group by t1.id HAVING sum(t2.approve='Y') = count(t1.id)