fetching records with column value constraints

I have a table like,

    Name              Privilege                                                           
     x                   1                                                                
     y                   2                                                                
     x                   3                                                                
     A                   1                                                                

Now I want to fetch only the names who has a Privilege 1 alone. ie., the reuslt should be,

    Name           Privilege                       开发者_如何学Go                                    
     A                1             

Can someone help me on this?

---

SELECT
    *
FROM
    <table> t
         LEFT JOIN
    <table> t2
         ON
             t.Name = t2.Name and
             t2.Privilege <> 1
WHERE
    t2.Name is null and
    t.Privilige = 1

Or you can alternatively write this as a NOT EXISTS clause and a correlated subquery.

---

Edit

From comment, where you're wanting to find "matches this set, and only this set" - put your conditions in another table (possibly a temp table or table variable), and then start looking at "relational division". A google search on this term brings up a Joe Celko article which is a decent read. The example under "Exact Division" would be of the sort you're talking about.

---

;WITH set1 AS (SELECT * FROM Privileges WHERE Privilege IN (1, 3))
SELECT up.UserId
FROM UserPrivileges up
INNER JOIN set1 ON set1.Privilege = set1.Privilege
GROUP BY up.UserId
HAVING COUNT(up.Privilege) = (SELECT COUNT(*) FROM set1);