Avoid showing duplicate Poptart Flavors when they're warm

Question

Please review the select statement below:

  SELECT 
    fc.flavorid,
    fc.flavorname,
    CASE 
      WHEN sa.flavorid IS NULL THEN 'No'
      ELSE 'Yes' END ) IsEaten
    CASE 
      WHEN t.tempid IS NULL THEN 'No' 
      ELSE 'Yes' END ) IsWarm

  FROM [poptart] p

    LEFT JOIN [diet] d ON d.Active = 1
    LEFT JOIN [toaster] ts ON p.poptartid = ts.poptartid AND d.dietname = ts.dietname
    LEFT JOIN [flavor] fl ON fl.poptartid = ts.poptartid
    LEFT JOIN [flavorcolor] fc ON fc.flavorid = fl.flavorid

    LEFT OUTER JOIN [stomach] sa ON sa.flavorid = fc.flavorid

    LEFT JOIN [poptart] p2 ON ts.poptartid = p2.poptartid
    LEFT JOIN [temp] t ON t.tempid = p2.tempid AND t.Active = 1

  GROUP BY fc.flavorid,fc.flavorname,sa.flavorid,t.tempid
  ORDER By fc.flavorname

As you can see below this returns one for each 'IsWarm' value for 'berry'. I want to only return distinct flavor names in this list, but the 'IsWarm' field is become a problem because it can be Yes or No and I want to only show Yes if Yes is available, not Yes and No so it won't pull duplicate flavornames, like 'berry':

    flavorid  |  flavorname  |  IsEaten  |  IsWarm
      123     |    berry     |    No     |   Yes
      123     |    berry     |    No     |   No
      234     |    fudge     |    Yes    |   No
      235     |    honey     |    No     |   No

How do I make this statement return only this..?

  flavorid  |  flavorname  |  IsEaten  |  IsWarm
    123     |    berry     |    No     |   Yes
    234     |    fudge     |    Yes    |   No
    235     |    honey     |    No     |   No

Any Suggestions?

use (Distinct) after select word [SELECT Distinct fc.flavorid, fc.flavorname, ...] — Georgian Citizen, Apr 21 '12 at 05:32
@mbayloon: using Distinct does the same thing.. Yes/No are Distinct. — Control Freak, Apr 21 '12 at 05:33
+1 for the pop-tart domain, if you can make it a tag I'll be impressed. Shades of http://stackoverflow.com/questions/5823903/automated-my-little-pony-detection-and-classification — HostileFork says dont trust SE, Apr 21 '12 at 05:57

Mahmoud Gamal · Accepted Answer · 2012-04-21T05:52:00.517

Try this:

;WITH FlavorsList
AS
( 
    //Your query here
    SELECT 
      fc.flavorid,
      fc.flavorname,
    CASE 
      WHEN sa.flavorid IS NULL THEN 'No'
      ELSE 'Yes' END ) IsEaten
    CASE 
      WHEN t.tempid IS NULL THEN 'No' 
      ELSE 'Yes' END ) IsWarm

  FROM [poptart] p

    LEFT JOIN [diet] d ON d.Active = 1
    LEFT JOIN [toaster] ts ON p.poptartid = ts.poptartid 
                           AND d.dietname = ts.dietname
    LEFT JOIN [flavor] fl ON fl.poptartid = ts.poptartid
    LEFT JOIN [flavorcolor] fc ON fc.flavorid = fl.flavorid

    LEFT OUTER JOIN [stomach] sa ON sa.flavorid = fc.flavorid

    LEFT JOIN [poptart] p2 ON ts.poptartid = p2.poptartid
    LEFT JOIN [temp] t ON t.tempid = p2.tempid AND t.Active = 1

  GROUP BY fc.flavorid,fc.flavorname,sa.flavorid,t.tempid
)
SELECT fl.flavorid, fl.flavorname, fl.IsEaten, Max(fl.IsWarm)
FROM FlavorsList fl
GROUP BY fl.flavorid, fl.flavorname, fl.IsEaten
ORDER BY fl.flavorname

What I've done here is wrapping your results with a query that will GROUP the results from your first query BY flavorid, flavorname, IsEated. Using MAX(IsWarm) as an aggregate function, will keep your IsWarm values DISTINCT for the vlafors names. I wrote this query using CTE just for simplicty and readability, but you can write it as a nested query.

Avoid showing duplicate Poptart Flavors when they're warm

1 Answers1