Return data from one list which applies to all data in a second list
-
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
-
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
-
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 -
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 -
Thanks to both of you for your help!
Paul, why do you say your second query is better?
-
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 -
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