Find items in table with 2 specific sizes

I have items table where the item code repeats as it has different sizes, variants.

I want to find items which has 2 specific sizes, ie size in both M/Y and Euro.

Items table:

Id size
1  0  
1  2Y
1  EU-15
2  2M
2  4M
3  0
3  2M-4M
3  EU-12
4  EU-11
4  EU-15

Required, I want to query for item id 1 and 3.

I was trying with SUM(), CASE but not able to figure it as it involves LIKE operator. (Size like '[^EU]%' and Size like 'EU%')

#Update:

With little hint, I could do it with 2 queries using temp table. Would be nice to see it in single query.

1st Query.

select id, 
case when size like '[^EU]%' then 'S' 
     when size like 'EU%' then 'EU' END as size 
     into #t from table

2nd Query.

select id, size from table
           where id in 
       ( select id from #t 
               group by id 
               having count(distinct(size))>1) 
order by id, size

Thanks.

Answer

I think you wanted Id with both EU% and non EU%

select t.Id
from   tbl t
group by t.Id
having count(distinct case when size like 'EU%' then 1 else 2 end) = 2

Advertisement

Answer