Interesting SQL issue

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开发者_如何学Python Type of both A and B.

Use the INTERSECT operator:

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

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

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')

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

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.

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);

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

This is very simple

Declare @t table([ID] INT, [Type] VARCHAR(2))
INSERT INTO @t SELECT 1, 'A' UNION ALL SELECT 2,'A' UNION ALL SELECT 3,'A' 
UNION ALL SELECT 3,'B' UNION ALL SELECT 4,'B' UNION ALL SELECT 5,'A' UNION ALL SELECT 5,'A'

;WITH CTE AS
(
    SELECT Rn = Row_NUMBER() OVER(PARTITION BY [ID],[TYPE] ORDER BY [ID])
    ,*
    FROM @t
)
SELECT ID 
FROM CTE 
WHERE Rn =1 AND ([Type]='A' or [Type]='B')
GROUP BY [ID]
HAVING (COUNT([ID])>1)

Output:

id

3

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)

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