T-SQL Two Where Clauses on same column

sql-server, t-sql, where-clause

Solution

You want something like this:

SELECT Name, Num
FROM tbl
WHERE Name IN (
    SELECT Name FROM tbl
    WHERE Num IN (10, 20)
    GROUP BY Name 
    HAVING COUNT(*) = 2 -- Must have all items in the set of 2
)
AND Num IN (10, 20) -- still need to restrict, since the set is not maximal

But there are other ways to skin the "set of items with all the following attributes" cat.

Problem

In SQL Server 2008, I have below Table Sample. ``` Name Num ---------- John 20 John 30 Peter 10 Peter 20 Peter 30 Marry 20 Marry 30 Mike 10 Mike 20 Mike 30 Phil 10 Phil 30 ``` I want records having Num = 10 and Num = 20. The query will return ``` Name Num ---------- Peter 10 Peter 20 Mike 10 Mike 20 ``` Many thanks.

Original source