May 29, 2009 at 12:24 pm
Hi there,
I'm sure there must be a set based solution to my question but sadly I am at a loss as to how to do this.
Given the example table and data below, I need a query to return a list of suppliers able to provide all of the items in a list
-- Create the table
USE tempdb
CREATE TABLE [FruitSuppliers]
(
SupplierID INT NOT NULL,
Product VARCHAR(10) NOT NULL
);
GO
--Populate the table
INSERT INTO FruitSuppliers(SupplierID, Product)
SELECT 1,'Bananas' UNION SELECT 1,'Apples' UNION
SELECT 1,'Pears' UNION SELECT 2,'Apples' UNION
SELECT 2,'Pears' UNION SELECT 3,'Pears'
Examples:
If I need pears then I can use supplier 1, 2 or 3.
If I need apples and pears I can use supplier 2 or 3
If I need bananas and pears then I can only use supplier 1
Any help would be greatly appreciated as this has been bugging me all afternoon.
Thanks,
Paul.
May 29, 2009 at 1:22 pm
Actually, if it's apples and pears, it's 1 or 2, not 2 or 3. 3 doesn't carry apples.
Here's a sample of how to do this kind of thing. Requires that you have a Numbers table. Tally table will work too (same thing, different name). If you don't have one, and aren't sure how to build one, ask and I'll give you a script for that too.
if object_id(N'tempdb..#FruitSuppliers') is null
begin
CREATE TABLE #FruitSuppliers
(
SupplierID INT NOT NULL,
Product VARCHAR(10) NOT NULL
);
--Populate the table
INSERT INTO #FruitSuppliers(SupplierID, Product)
SELECT 1,'Bananas' UNION SELECT 1,'Apples' UNION
SELECT 1,'Pears' UNION SELECT 2,'Apples' UNION
SELECT 2,'Pears' UNION SELECT 3,'Pears';
end
if object_id(N'tempdb..#T') is not null
drop table #T;
declare @FruitsDesired varchar(max);
select @FruitsDesired = 'Bananas,Pears';
select
substring(@FruitsDesired + ',', Number,
charindex(',', @FruitsDesired + ',', Number) - Number) as Item
into #T
from dbo.Numbers
where Number <= len(@FruitsDesired)
and substring(',' + @FruitsDesired, Number, 1) = ','
order by Number;
select SupplierID
from #FruitSuppliers Suppliers
inner join #T T
on Suppliers.Product = T.Item
group by SupplierID
having count(*) =
(select count(*)
from #T);
Does that help?
By the way, this looks like a homework question. Is it, or did you just annonymize the data a bit?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 29, 2009 at 2:16 pm
That's perfect GSquared, thank you so much!
Apologies for the error in my example. I spent so much time worrying about anonymising the data that I managed to let that one slip through.
I had tried various queries similar to yours but it seems the HAVING clause is what I was missing.
We had 4 people in the office trying to figure this one out today and no one managed it with a set-based answer.
Thanks again for your help 🙂
Paul.
June 1, 2009 at 7:06 am
You're welcome. Glad I could help.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply