title: How to check if at least one of a group of rows has a specific value
body: <p>I need to find all rows that are part of any group that meets certain conditions. </p>
  
  <p>I am defining a group as several rows that share a value in the Group column.<br>
  A relevant group must contain at least one row with Eligible set to true and at least two of the rows in the group must be different from each other in any column other than Group or Eligible.</p>
  
  <p>Example Table</p>
  
  <pre><code>Group    LastName    FirstName   Eligible
  ==========================================
   1       Smith       John          True
   1       Smith       John          False
   2       Doe         Beth          True
   2       Doe         Jane          False
   2       Doe         Jane          False
   3       Ward        Bill          True
   4       Adams       Sally         True
   4       Grimes      Sally         True
  </code></pre>
  
  <p>Desired Result  </p>
  
  <pre><code>Group    LastName    FirstName   Eligible
  ==========================================
   2       Doe         Beth          True
   2       Doe         Jane          False
   2       Doe         Jane          False
   4       Adams       Sally         True
   4       Grimes      Sally         True
  </code></pre>
  
  <p>The following query gets me close</p>
  
  <pre><code>SELECT *
  FROM ExampleTable
  WHERE Group in 
      (SELECT Group
      FROM ExampleTable
      GROUP BY Group
      HAVING count(distinct LastName) &gt; 1 or count(distinct FirstName) &gt; 1)
  </code></pre>
  
  <p>The problem is that it's returning too many results because it's not taking into consideration whether one of the records in the group is marked as eligible.  </p>
  
  <p>I'm probably missing something simple, but I can't figure out how to check if one record in the group has Eligible set to true without checking if all of them are set to true.</p>