question

Goldie Lesser avatar image
Goldie Lesser asked

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
sql-server-2005t-sql
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tom Staab avatar image
Tom Staab answered
SELECT c.Name
FROM @FlowerColors fc
    INNER JOIN @Colors c
        ON c.ID = fc.ColorID
GROUP BY c.Name
HAVING COUNT(*) = (SELECT COUNT(*) FROM @Flowers)
ORDER BY c.Name
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

CirqueDeSQLeil avatar image
CirqueDeSQLeil answered

Hmmm. I would say go with Paul's answer that he posted in the SSC forum at http://www.sqlservercentral.com/Forums/Topic879551-338-1.aspx

It is a very good query and solves the issue.

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yes, that is what I am doing. Sorry for posting in 2 places.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.