SQL common record from same table

Question

in source table, person can be part of n number of states. in target I need the common state for that pool. in below example I need CA and FL.

Output:

Answer 1

Concept:

Get a distinct count of states. Then, ensure the count matches the number of pool mbrs; if so display that state.

We get the distinct count of states via a subquery which always returns 1 row and cross join it to the results allowing us to limit by it.

Thus if a state is in every pool member, it should be included in the results.

You didn't specify what flavor of SQL. The below was tested in SQL server. I think this is pretty agnostic except for the CTE use; but the rest should be pretty valid across most RDBMS platforms.

Rextester link: https://rextester.com/BSRS28955

with cte  AS
(Select 'A' Pool, 1 PoolMbr, 'CA' State UNION ALL
 Select 'A', 1, 'FL' UNION  ALL
 Select 'A', 1, 'AZ'  UNION  ALL
 Select 'A', 2, 'FL'  UNION  ALL
 Select 'A', 2, 'CA'  UNION  ALL
 Select 'A', 3, 'CA'  UNION  ALL
 Select 'A', 3, 'FL'  UNION  ALL
 Select 'A', 3, 'NC'  UNION  ALL
 Select 'A', 3, 'AZ'  UNION  ALL
 Select 'A', 4, 'CA'  UNION  ALL
 Select 'A', 4, 'FL' 
    )
  
SELECT Pool, State
FROM cte
CROSS JOIN (Select count(Distinct state) cnt from cte) z
GROUP BY Pool, State, z.cnt
having z.cnt = count(distinct poolmbr)

Giving us:

+----+------+-------+
|    | Pool | State |
+----+------+-------+
|  1 | A    | CA    |
|  2 | A    | FL    |
+----+------+-------+

Perhaps you're after this instead: all I did was switch poolmbm and state and remove distinct off of state.

https://rextester.com/YJYGS12532

with cte  AS
(Select 'A' Pool, 1 PoolMbr, 'CA' State UNION ALL
 Select 'A', 1, 'FL' UNION  ALL
 Select 'A', 1, 'AZ'  UNION  ALL
 Select 'A', 2, 'FL'  UNION  ALL
 Select 'A', 2, 'CA'  UNION  ALL
 Select 'A', 3, 'CA'  UNION  ALL
 Select 'A', 3, 'FL'  UNION  ALL
 Select 'A', 3, 'NC'  UNION  ALL
 Select 'A', 3, 'AZ'  UNION  ALL
 Select 'A', 4, 'CA'  UNION  ALL
 Select 'A', 4, 'FL'
 
    )

SELECT Pool, State
FROM cte
CROSS JOIN (Select count(distinct PoolMbr) cnt from cte) z
GROUP BY Pool, State, z.cnt
having z.cnt = count(State)

The above approach switches the count of state and pool member. This solves the problem wher eif you added a pool member 5 for CA, CA would fall off the list; while FL would remain I believe this may have been the "Static" issue you were referring to.