Have Geek Brain

Displaying null data in SQL query?

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 request for a list of members in each team:

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

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

MRT2: 7, MRT3: 14

I'm not sure how to set up my query to list all 4 commands:

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

I fiddled with subqueries to fix this with no luck. Any ideas? Thanks to

0


a source to share


4 answers


try to select from the list 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 idea of ​​what it does.

It says

Give me all the teams from the team and then for each count of matches in the member table, even if there is no match.



if you use

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

it means

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

+5


a source 


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


+3


a source


I did this test and it 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;
+2


a source


A proper join to the TEAM table should fix the problem.

0


a source






More articles:


All Articles