SQL Server Performance Forum – Threads Archive

Problem with Select Statement

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)

---

]]>