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:
x
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.
Advertisement
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