Question: Suppose I have one table with the following values and columns:

```
ID1 | ID2
1   | 1
2   | 1
3   | 1
4   | 1
4   | 2
3   | 3
4   | 3
4   | 4
4   | 4

```

I'd like to retrieve the ID2 values that belong **exclusively** to records where ID1 = 4. So for the above example, I'd like to see the following response:

```
ID1 | ID2
4   | 2
4   | 4

```

Answer: 
Try working it out [contrapositively](http://en.wikipedia.org/wiki/Contraposition) like this.

Finding all elements where ID1 **is only** 4 is the same as finding all elements that **don't not** have ID1 = 4.

```
CREATE TABLE #temp (ID1 NVARCHAR(10), ID2 NVARCHAR(10))
INSERT INTO #temp(ID1,ID2) VALUES  (N'1',N'1')
INSERT INTO #temp(ID1,ID2) VALUES  (N'2',N'1')
INSERT INTO #temp(ID1,ID2) VALUES  (N'3',N'1')
INSERT INTO #temp(ID1,ID2) VALUES  (N'4',N'1')
INSERT INTO #temp(ID1,ID2) VALUES  (N'4',N'2')
INSERT INTO #temp(ID1,ID2) VALUES  (N'3',N'3')
INSERT INTO #temp(ID1,ID2) VALUES  (N'4',N'3')
INSERT INTO #temp(ID1,ID2) VALUES  (N'4',N'4')
INSERT INTO #temp(ID1,ID2) VALUES  (N'4',N'4')

SELECT * FROM #temp AS t

SELECT DISTINCT * FROM #temp AS t
WHERE id2 NOT IN (SELECT ID2 FROM #temp AS t WHERE ID1 <> 4)

```
There are a few ways to do this:

```
SELECT t1.id1, t1.id2
  FROM mytable t1
 WHERE t1.id1 = 4
   AND NOT EXISTS ( SELECT 1 FROM mytable t2
                     WHERE t2.id2 = t1.id2
                       AND t2.id1 != 4 );

```

or:

```
SELECT id1, id2 FROM (
    SELECT id1, id2
      FROM mytable
     GROUP BY id1, id2
    HAVING COUNT(*) = 1
) WHERE id1 = 4;

```

or:

```
SELECT id1, id2 FROM (
    SELECT id1, id2, COUNT(*) OVER ( PARTITION BY id2 ) AS cnt
      FROM mytable
) WHERE id1 = 4
    AND cnt = 1;

```