Displaying zero-valued data in an SQL Query?

Question

I have the following two tables (simplified for this question):

CREATE TABLE team (
 teamID CHAR(6) NOT NULL PRIMARY KEY); 

CREATE TABLE member (
 memberID CHAR(7) NOT NULL PRIMARY KEY,
 teamID CHAR(6) NOT NULL REFERENCES team(teamID) );

I also have the following query, which is to list the number of members in each team:

SELECT teamID, count(memberID) AS [noOfMembers]
FROM member
GROUP by teamID;

However, I have four teams (MRT1, MRT2, MRT3 and MRT4). My members in my table only belong to teams 2 and 3, so when I run the query I get the following output:

MRT2: 7, MRT3: 14

I'm not sure how I can adjust my query to list all 4 teams like so:

MRT1: 0, MRT2: 7, MRT3: 14, MRT4: 0

I have been messing with subqueries to fix this without any luck. Any ideas? Thanks

Answer 1

try selecting from TEAM left JOIN-ing on Member

SELECT Team.Teamid, count(memberid)
FROM 
    TEAM
LEFT OUTER JOIN 
     Member on Member.teamid = Team.Teamid
GROUP by Team.Teamid

Just to give you some background as to what this is doing.

It says

Give me all teamids from team and then for each one count the matches in the member table even if there are no matches.

if you use

SELECT Team.Teamid, count(memberid)
FROM 
    TEAM
INNER JOIN 
     Member on Member.teamid = Team.Teamid
GROUP by Team.Teamid

this translates to

Give me all teamids from team and then for each one count the matches in the member table but only if there are matches.

Answer 2

A right join to the TEAM table should solve the problem.

Answer 3

SELECT teamID, count(memberID) AS [noOfMembers]
FROM team
LEFT JOIN member
ON team.teamID = member.teamID
GROUP by teamID;

Answer 4

I did this test, and worked for me

CREATE TABLE team (
 teamID CHAR(6) NOT NULL PRIMARY KEY); 

CREATE TABLE member (
 memberID CHAR(7) NOT NULL PRIMARY KEY,
 teamID CHAR(6) NOT NULL REFERENCES team(teamID) );

INSERT INTO team (teamID) VALUES ('T1')
INSERT INTO team (teamID) VALUES ('T2')
INSERT INTO team (teamID) VALUES ('T3')
INSERT INTO team (teamID) VALUES ('T4')

INSERT INTO member (memberID, teamID) VALUES ('M1', 'T1')
INSERT INTO member (memberID, teamID) VALUES ('M2', 'T1')
INSERT INTO member (memberID, teamID) VALUES ('M3', 'T1')
INSERT INTO member (memberID, teamID) VALUES ('M4', 'T3')

SELECT Team.teamID, count(member.memberID) AS [noOfMembers]
FROM Team LEFT JOIN member ON Member.teamID = Team.teamID
GROUP BY ALL Team.teamID;