How to check if at least one of a group of rows has a specific value

Question

I need to find all rows that are part of any group that meets certain conditions.

I am defining a group as several rows that share a value in the Group column.
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.

Example Table

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

Desired Result

Group    LastName    FirstName   Eligible
==========================================
 2       Doe         Beth          True
 2       Doe         Jane          False
 2       Doe         Jane          False
 4       Adams       Sally         True
 4       Grimes      Sally         True

The following query gets me close

SELECT *
FROM ExampleTable
WHERE Group in 
    (SELECT Group
    FROM ExampleTable
    GROUP BY Group
    HAVING count(distinct LastName) > 1 or count(distinct FirstName) > 1)

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.

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.

Answer 1

Add one more condition which checks if the group has atleast one eligible=True value.

SELECT *
FROM ExampleTable
WHERE Group in 
    (SELECT Group
    FROM ExampleTable
    GROUP BY Group
    HAVING (count(distinct LastName) > 1 or count(distinct FirstName) > 1) 
    and count(case when eligible='True' then 1 end) >= 1
    )

Answer 2

You can identify the groups using aggregation logic:

SELECT Group
FROM ExampleTable
GROUP BY Group
HAVING (count(distinct LastName) > 1 or count(distinct FirstName) > 1)) AND
       MAX(eligible) = 'True';

Note: This assumes eligible is a string. The logic on eligible could be implemented in other ways, depending on the type and database.

Then your query is basically there:

SELECT et.*
FROM ExampleTable et
WHERE et.Group IN (SELECT Group
                   FROM ExampleTable
                   GROUP BY Group
                   HAVING (count(distinct LastName) > 1 or count(distinct FirstName) > 1)) AND
                          MAX(eligible) = 'True'
                  );