Return data from one list which applies to all data in a second list

Question

question

Goldie Lesser asked Mar 9, '10

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-2005 t-sql

10 |1200

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

Answer 1 · 2010-03-09T15:30:36Z

Tom Staab answered Mar 9, '10

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

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

Answer 2 · 2010-03-09T16:28:26Z

CirqueDeSQLeil answered Mar 9, '10

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

10 |1200

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

Goldie Lesser commented · Mar 10, 2010 at 11:57 AM

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

0 ·

question