SQL: How to pick children who can play all the games?-CodePudding

I have a table with columns: child_name and game_name. For example:

child_name     game_name
-------------------------
Phineas          Monopoly
Ferb             Chess
Phineas          Chess
Ferb             Monopoly
Raj              Chess
Perry            Monopoly
Candice          Basketball

I want to select the children who can play both Chess and Monopoly (that is, Phineas and Ferb). I was thinking along the lines of using the 'IN' or the 'ALL' operator but haven't been able to use them appropriately for this.

CodePudding user response：

The code for this would look something like this

WITH COUNTING_CTE
AS
(
SELECT
*,
CASE WHEN game_name = X THEN 1
CASE WHEN game_name = Y THEN 1
ELSE 0
END AS COUNTING_COL
FROM TABLE
), MATH_CTE
AS
(
SELECT
child_name,
SUM(COUNTING_COL) TOTAL_NUM
FROM COUNTING_CTE
GROUP BY CHILD_NAME
)
SELECT
child_name
FROM MATH_CTE
WHERE 1=1
AND TOTAL_NUM >= 2

You could also use a having clause in the "math_cte" section if you wanted to instead of making another CTE just would be personal preference at that point

CodePudding user response：

You can try to use HAVING with condition aggregate function.

Query 1:

SELECT child_name
FROM T
GROUP BY child_name
HAVING 
    COUNT(CASE WHEN game_name = 'Monopoly' THEN 1 END) > 0  
AND
    COUNT(CASE WHEN game_name = 'Chess' THEN 1 END) > 0

Results:

| child_name |
|------------|
|       Ferb |
|    Phineas |

CodePudding user response：

Here is a general solution that selects those kids that can play all games.

With CTE As (
Select Child_Name, Count(Game_Name) GameCount
From GameTable
Group By Child_Name)
Select Child_Name From CTE 
Where GameCount=(Select Count(Distinct Game_Name) From GameTable)

First, we get a list of all children and the count of games they can play. Then, we pick only those children whose count matches the total number of games.

This will work for any number of children or games.