Interesting SQL issue

Question

I have a SQL problem I am trying to digest. I am using SQL Server 2005.

In a table I have data as such:

ID     Type
1        A
2        A
3        A
3        B
4        B

I need to find all of the IDs that have a Type of both A and B.

Answer 1

Use the INTERSECT operator:

   SELECT DISTINCT ID FROM [Table] WHERE Type = 'A'
   INTERSECT
   SELECT DISTINCT ID FROM [Table] WHERE Type = 'B'