Help writing this query 1

[pandatime]

Hi,

I have a query that I know is probably easy to write, but I'm just missing it for some reason.

Here's the basic query:

select id, sku from myTable
into #temp
where sku = 'abc'
or sku = 'def'
or sku = 'ghi'
or sku in ('yy', 'zz')
group by profile_id, sku
order by profile_id, sku

Basically I eventually want to get every "id" who has sku 'abc' + 'def' + 'ghi' + 'yy' OR 'zz'.

So, if "id" 1 has sku's 'abc' + 'def' + 'ghi' + 'yy' OR 'abc' + 'def' + 'ghi' + 'zz', that's good.

But if it has sku's 'abc' + 'def' + 'yy' + 'zz' I don't want this id selected.

The above query is just an attempt to narrow my working data set. So if anyone has any suggestions as to do this in one query, or using the results of the above query, I'd greatly appreciate it!

Thanks

 

[gmmastros]

Should we assume that the SKU's are in multiple rows?

Ex:
[tt]
id SKU
-- ---
1 abc
1 yy
2 abc
2 def
2 zz
[/tt]

If I understand correctly, you want to return ID's where there is at least one row with abc, another row with def, another row with ghi and then at least another row with yy or zz. Do I understand correctly?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[pandatime]

Yes, on both points.

Thank you

 

[bborissov]

Could these SKUs be duplicated?
In other wordds could you have such data:
[tt]
1 abc
1 abc
1 abc
1 abc
1 yy

2 abc
2 abc
2 def
2 def
2 def
2 zz
[/tt]

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[pandatime]

Yes, it's possible to duplicate the data. That's why I'm grouping on id and sku.

Thanks

 

[pandatime]

Correction, the query should be:

select id, sku
from myTable
into #temp
where sku = 'abc'
or sku = 'def'
or sku = 'ghi'
or sku in ('yy', 'zz')
group by id, sku
order by id, sku

 

[gmmastros]

Does this work for you?

Declare @Temp Table(Id Int, SKU VarChar(10))

Insert Into @Temp Values(1, 'abc')
Insert Into @Temp Values(1, 'def')
Insert Into @Temp Values(1, 'ghi')
Insert Into @Temp Values(1, 'yy')

Insert Into @Temp Values(2, 'abc')
Insert Into @Temp Values(2, 'def')
Insert Into @Temp Values(2, 'ghi')
Insert Into @Temp Values(2, 'zz')

-- 3 does not have yy or zz
Insert Into @Temp Values(3, 'abc')
Insert Into @Temp Values(3, 'def')
Insert Into @Temp Values(3, 'ghi')

-- 4 does not have ghi
Insert Into @Temp Values(4, 'abc')
Insert Into @Temp Values(4, 'def')
Insert Into @Temp Values(4, 'zz')

Select   Id
From	 @Temp
Group By Id
Having   Count(Case When SKU = 'abc' Then 1 End) > 0
         And Count(Case When SKU = 'def' Then 1 End) > 0
         And Count(Case When SKU = 'ghi' Then 1 End) > 0
         And Count(Case When SKU in ('yy','zz') Then 1 End) > 0

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[pandatime]

@gmmastros:

Yes that works great - thanks so much