I have two Tables Table1 and Table2 having values Table1 -------------------- A B ------ 1 a 2 b 3 c 4 d 5 e Table2 -------------------- X Y Z ------------ 1 a 1 2 a 0 3 a 1 4 b 1 5 b 1 6 c 0 7 c 0 8 d 1 9 e 0 Table1 has Primary Column as A and Table2 X Table2 is the description table of the column B of Table1 Table1 and Table 2 are linked using the column B and Y Table2 has a flag Field Z (1/0) My need is to find those rows in Table1 which has all the values of its corresponding column Z in Table2 as 1 My query return those columns which have any of the Z fields of Table2 as 1 My query is select Table1.A from Table1, Table2 where Table1.B = Table2.Y and Table2.Z = 1 group by Table1.A My Query Returns answer as 1, 2, 4 whereas i need the answer as 2 and 4 ie not showing 1 which has a 0 value in the 2nd row of Table2 Please Help

select a.A from table1 a where exists(select * from table2 b where b.y = a.b and b.z = 1) and not exists(select * from table2 c where c.y = a.b and c.z = 0)

You need to do a 'negative lookup' here, check the NOT EXISTS subquery ... SELECT DISTINCT T1.A FROM dbo.Table1 T1 INNER JOIN dbo.tbl2 T2 ON T1.B = T2.Y WHERE T2.Z = 1 AND NOT EXISTS (SELECT * FROM dbo.tbl2 Tx WHERE Tx.Y = T2.Y AND Tx.Z = 0)