title: SELECT DISTINCT HAVING Count unique conditions
body: <p>I've searched for an answer on this but can't find quite how to get this distinct recordset based on a condition.  I have a table with the following sample data:</p>
  
  <pre><code>Type    Color   Location    Supplier
  ----    -----   --------    --------
  Apple   Green   New York    ABC
  Apple   Green   New York    XYZ
  Apple   Green   Los Angeles ABC
  Apple   Red     Chicago     ABC
  Apple   Red     Chicago     XYZ
  Apple   Red     Chicago     DEF
  Banana  Yellow  Miami       ABC
  Banana  Yellow  Miami       DEF
  Banana  Yellow  Miami       XYZ
  Banana  Yellow  Atlanta     ABC
  </code></pre>
  
  <p>I'd like to create a query that shows the count of unique locations for each distinct Type+Color where the number of unique locations is more than 1, e.g.</p>
  
  <pre><code>Type    Color   UniqueLocations
  ----    -----   --------
  Apple   Green   2
  Banana  Yellow  2
  </code></pre>
  
  <p>Note that {Apple, Red, 1} doesn't appear because there is only 1 location for red apples (Chicago).  I think I've got this one (but perhaps there is a simpler method).  I'm using:</p>
  
  <pre><code>SELECT Type, Color, Count(Location) FROM
  (SELECT DISTINCT Type, Color, Location FROM MyTable)
  GROUP BY Type, Color HAVING Count(Location)&gt;1;
  </code></pre>
  
  <p>How can I create another query that lists the <code>Type, Color</code>, and <code>Location</code> for each distinct <code>Type,Color</code> when the count of unique locations for that <code>Type,Color</code> is greater than 1?  The resulting recordset would look like:</p>
  
  <pre><code>Type    Color   Location
  ----    -----   --------
  Apple   Green   New York
  Apple   Green   Los Angeles
  Banana  Yellow  Miami
  Banana  Yellow  Atlanta
  </code></pre>
  
  <p>Note that <code>Apple, Red, Chicago</code> doesn't appear because there is only 1 location for red apples.  Thanks!</p>