How do I query teams with no members in my schema

Question

In my database schema, I have clubs, teams and players and I want to create a report that counts the total number of teams in a club, as well as the number of teams that have no players. The output would be something like:

CLUB      #TEAMS  #EMPTYTEAMS
Cobras    5       0
Eagles    7       2

My schema is described in the following pseudo-code:

TABLE CLUB
  ID int
  NAME string

TABLE TEAM
  ID int
  NAME string

TABLE PLAYER
  ID int
  NAME string

TABLE CLUB_TEAM
  ID int
  CLUB_ID int   -- foreign key into CLUB
  TEAM_ID int   -- foreign key into TEAM

TABLE MEMBERSHIP
  ID int
  PLAYER_ID int -- foreign key into PLAYER
  TEAM_ID int   -- foreign key into TEAM

Some sample data:

CLUB
0  Eagles
1  Cobras

TEAM
0  Eagles U10
1  Eagles U11
2  Eagles U12
...
7  Cobras U11
8  Cobras U12
9  Cobras U13
...

PLAYER
0 John Doe
1 Jane Doe
...

CLUB_TEAM
0 0 0  - The Eagles Club has an U10 team
1 0 1
2 0 2
...
7 1 7
8 1 8
9 1 9

MEMBERSHIP
0 0 7  - John plays for Cobras U11
1 1 9  - His older sister, Jane, plays for Cobras U13

I've managed to develop a query that will give me the number of teams in each club:

  SELECT CLUB.ID, CLUB.NAME, COUNT(CLUB_TEAM.ID) AS "#TEAMS"
  FROM CLUB
  INNER JOIN CLUB_TEAM
    ON CLUB_TEAM.CLUB_ID = CLUB.ID
  INNER JOIN TEAM
    ON CLUB_TEAM.TEAM_ID = TEAM.ID
  GROUP BY CLUB.ID

However, I haven't been able to extend this query to include team membership data and count teams with no players. Any suggestions?

Please add a few rows of sample data and the expected result. — The Impaler, Sep 01 '20 at 02:07
The expected result is at the top of the question. I'll add some sample data. — dave, Sep 01 '20 at 02:10
You typically GROUP BY the same columns as you SELECT, except those who are arguments to set functions. — jarlh, Sep 01 '20 at 06:32
I'm using PostgreSQL, however I'd like to use standard SQL if possible. — dave, Sep 01 '20 at 06:43

score 1 · Answer 1 · answered Sep 03 '20 at 06:57

I managed to figure out how to track down the empty teams and my solution was similar to the second part of @user9601310's.

SELECT CLUB.ID, CLUB.NAME, COUNT(MEMBERSHIP.TEAM_ID) AS "#MEMBERS"
FROM CLUB
INNER JOIN CLUB_TEAM
  ON CLUB_TEAM.CLUB_ID = CLUB.ID
INNER JOIN TEAM
  ON CLUB_TEAM.TEAM_ID = TEAM.ID
LEFT JOIN MEMBERSHIP -- LEFT JOIN to capture teams with no members
  ON MEMBERSHIP.TEAM_ID = TEAM.ID
GROUP BY CLUB.ID, TEAM.ID
HAVING COUNT(MEMBERSHIP.TEAM_ID) = 0

From there, I wrapped this query (...) in the following manner:

SELECT CLUB.ID, COUNT(#MEMBERS) AS "#EMPTYTEAMS"
FROM ( ... ) AS "QUERY"
GROUP BY CLUB.ID

Which gave me the number of empty teams on a per-club basis.

I then combined my two queries - the one above and the one from the question - based on the idea in this question in the following manner:

SELECT SET1.ID, CLUB.NAME, "#TEAMS", "#EMPTYTEAMS"
FROM ( query 1 ) SET1
INNER JOIN ( query 2 ) SET2
ON SET1.ID = SET2.ID

Which gave me the desired result. Not exactly elegant, but the inner queries are fairly straight-forward, as is their combination.

score 0 · Answer 2 · answered Sep 01 '20 at 08:49

Perhaps this will help. As there's no sample data, I haven't been able to test it so there may well be a syntax error (or two!), but hopefully you get the idea. Basically you summarize the membership table to have one row giving a member count per team and then LEFT JOIN that to your original query. The COALESCE handles the case when a team has no members and therefore returns a NULL value on the LEFT JOIN which gets translated to a zero. I believe PostgreSQL supports common table expressions (i.e. the WITH clause I've used here), which is pretty standard for SQL databases these days.

WITH team_membership AS (
     SELECT team_id, COUNT(*) AS membercount 
       FROM membership
)
SELECT club.id,
       club.name,
       COUNT(club_team.id) AS "#TEAMS",
       SUM(CASE WHEN COALESCE(membercount, 0) = 0 THEN 1 ELSE 0 END) AS "#EMPTYTEAMS"   
FROM   club
       INNER JOIN club_team
               ON club_team.club_id = club.id
       INNER JOIN team
               ON club_team.team_id = team.id
       LEFT JOIN team_membership
              ON club_team.team_id = team_membership.team_id
GROUP  BY club.id,
          club.name

How do I query teams with no members in my schema

2 Answers2