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.
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
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