March 9, 2010 at 10:35 am
I am trying write a query that has me stumped. The real data is proprietary, so I have provided a similar question below.
Based on the given data, I am trying to write a query to retrieve a list of colors which are available for all flowers. The expected results would be White, and Pink.
-- sample DDL
DECLARE @Flowers TABLE
(
ID INT IDENTITY(1,1)
,Name VARCHAR(20)
)
DECLARE @Colors TABLE
(
ID INT IDENTITY(1,1)
,Name VARCHAR(20)
)
DECLARE @FlowerColors TABLE
(
FlowerID INT
,ColorID INT
)
-- sample DATA
INSERT INTO @Flowers
SELECT 'Rose'
UNION ALL SELECT 'Tulip'
UNION ALL SELECT 'Lily'
UNION ALL SELECT 'Carnation'
INSERT INTO @Colors
SELECT 'White'
UNION ALL SELECT 'Violet'
UNION ALL SELECT 'Pink'
UNION ALL SELECT 'Red'
UNION ALL SELECT 'Yellow'
UNION ALL SELECT 'Orange'
INSERT INTO @FlowerColors(FlowerID, ColorID)
SELECT 1,1
UNION ALL SELECT 2,1
UNION ALL SELECT 3,1
UNION ALL SELECT 4,1
UNION ALL SELECT 3,6
UNION ALL SELECT 1,4
UNION ALL SELECT 1,3
UNION ALL SELECT 2,3
UNION ALL SELECT 3,3
UNION ALL SELECT 4,3
UNION ALL SELECT 2,5
UNION ALL SELECT 2,6
UNION ALL SELECT 2,4
UNION ALL SELECT 2,2
UNION ALL SELECT 1,5
SELECT *
FROM @Flowers
SELECT *
FROM @Colors
SELECT *
FROM @FlowerColors
March 9, 2010 at 11:05 am
Here's a "quick and dirty" way.
(Note: I used temp tables in my test. You'll have to mod accordingly)
SELECT Colors.[name]
FROM #FlowerColors FlowerColors
INNER JOIN #Colors Colors
ON FlowerColors.ColorID = Colors.ID
GROUP BY Colors.[name]
HAVING COUNT(DISTINCT FlowerID) = (SELECT COUNT(*) FROM #Flowers)
I can't help but think there's a more elegant way to solve this, I just don't know what that is. I guess a LEFT JOIN on the flowers table instead of the COUNT(*) in the HAVING clause might be another option. I was in a similar situation many years ago and a little experimentation led me to this approach.
HTH
- Mike
March 9, 2010 at 11:25 am
Nice problem.
SELECT C.Name
FROM @Colors C
WHERE NOT EXISTS
(
-- All flowers
SELECT ID
FROM @Flowers
EXCEPT
-- Except those flowers in the current colour
SELECT FlowerID
FROM @FlowerColors FC
WHERE FC.ColorID = C.ID
);
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 9, 2010 at 11:38 am
Ever so slightly better:
SELECT C.Name
FROM @Colors C
WHERE NOT EXISTS
(
-- There is no flower
SELECT *
FROM @Flowers F
WHERE NOT EXISTS
(
-- That doesn't come in the current colour
SELECT *
FROM @FlowerColors FC
WHERE FC.FlowerID = F.ID
AND FC.ColorID = C.ID
)
);
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 9, 2010 at 11:41 am
Thanks to both of you for your help!
Paul, why do you say your second query is better?
March 9, 2010 at 11:58 am
Goldie Graber (3/9/2010)
Paul, why do you say your second query is better?
Compare the execution plans 😉
EXCEPT
NOT EXISTS
The SORT DISTINCT is omitted from the NOT EXISTS plan. Primary keys on the tables would make this difference go away I think.
edit: Yes it does make the difference disappear.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 9, 2010 at 2:08 pm
Goldie Graber (3/9/2010)
Thanks to both of you for your help!
Pleasure to be of assistance. 🙂
- Mike
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply