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

0

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.

Link: How to check if at least one of a group of rows has a specific value
Source: Stack Sql

Share.

About Author

Leave A Reply