Select rows with same column A but different column B

Question

ID          Zip        Room
----------- ---------- ------
317         94087      S105
318         94087      L603
1739        94404-1801 L603
1823        94401-2129 L603
1824        94401-2129 L603
2135        94404-1801 L603
2268        95136-1459 S604
2269        95136-1459 S604
3704        92673-6417 L402
4479        93454-9670 L402
4480        93454-9670 L402
4782        92395-4681 L402
4783        92395-4681 L402
4852        92886-4411 L402
4853        92886-4411 L402
4959        92673-6417 L402
5153        91773-4028 L402
5202        91773-4028 L402
5211        91765-2959 L402
5212        91765-2959 L402
5388        92336-0605 L402
5392        92336-0605 L402
5727        92870      L402
5728        92870      L402
5831        92557      L402
5916        92557      L402

How do I select ID's that has THE SAME zip but different Room ?

In the table above, I want the result to be:

ID          Zip        Room
----------- ---------- ------
317         94087      S105
318         94087      L603

Using SQL Server 2008

Answer 1

You can use EXISTS:

SELECT ID, Zip, Room 
FROM dbo.Table t1
WHERE EXISTS
(
   SELECT 1 FROM dbo.Table t2
   WHERE t2.Zip = t1.Zip
   AND t2.Room <> t1.Room
)