Interesting SQL problem

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.

This is not a homework problem. It's a real work issue I'm trying to resolve.

Thanks

Answer 1

select distinct a.id 
from table a
join table b on a.id=b.id
where a.type='A'
and b.type='B';

Answer 2

Use the INTERSECT operator:

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

Answer 3

With a semi-join (no sorting, only index seek on B):

select a.id from table a
    where a.type = 'A'
      and exists (select * from table b where a.id = b.id and b.type = 'B')

Answer 4

this would help if there are "unknown" amounts of types and you want to find all IDs which have all of types

select id from yourtable group by id having count(*)=(select  count(distinct type) from yourtable)

Answer 5

If you want to abstract the problem a little bit and find cases where rows with the same id contain different values in the type column, you can check for <> like this:

DECLARE @TestTable TABLE (thisid int, thisval varchar(1))

INSERT INTO @TestTable VALUES  (1, 'A')
INSERT INTO @TestTable VALUES  (2, 'A')
INSERT INTO @TestTable VALUES  (3, 'A')
INSERT INTO @TestTable VALUES  (3, 'B')
INSERT INTO @TestTable VALUES  (4, 'B')

SELECT DISTINCT thisid
FROM @TestTable a
WHERE EXISTS
( SELECT * 
FROM @TestTable b
WHERE a.thisid=b.thisid AND a.thisval<>b.thisval)
-- www.caliberwebgroup.com

This returns:

3

Answer 6

select id, count(type = 'A') as a_count, count(type = 'B') as b_count
from your_table
group by 1
having a_count > 0 and b_count > 0;

At least, this works in sane SQL environments. Dunno if it works in yours.

Answer 7

I was not looking at other answers, but still posting. lol

SELECT distinct t1.ID
FROM table1 AS t1
WHERE exists 
   (select t2.ID from table1 t2 where t2.type="A" and t2.ID=t1.ID) 
   and exists 
   (select t3.ID from table1 t3 where t3.type="B" and t3.ID=t1.ID);

Answer 8

SELECT Id FROM tableX AS x, tableX AS y WHERE x.id = y.id AND x.type = 'A' AND y.type = 'B'

Answer 9

This is very simple

select id 
from @t 
where type='A' or type='B'
group by id
having (COUNT(id)>1)

Output:

id

3

Answer 10

select id
from idtypedata
group by id
having 
sum(
    case type
        when 'A' then 1
        when 'B' then 2
        -- when 'C' then 4
        -- when 'D' then 8
    end
    ) & 1 = 1
And 
sum(
    case type
        when 'A' then 1
        when 'B' then 2
        -- when 'C' then 4
        -- when 'D' then 8
    end
    ) & 2 = 2