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

    );

  • 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

  • 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.

  • 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