How to display the other remaining record?-CodePudding

I'm trying to display the number of appointments handled by all staff. However, I'm only able to display the staff that have handled appointment. The staff that handle 0 appointment are not showing.

For the example below, I have 20 staffs but only able to display 18 staffs. The 2 remaining staff does not show up due to they handled 0 appointments.

Is there any way to display the staff that handled 0 appointments too?

CREATE TABLE Staffs (
id NUMBER NOT NULL,
name VARCHAR(30) NOT NULL,
email VARCHAR(30) NOT NULL,
phoneNo VARCHAR(11) NOT NULL,
role VARCHAR(20) NOT NULL,
commission NUMBER NOT NULL,
PRIMARY KEY (id)
);


CREATE TABLE Appointments (
id NUMBER NOT NULL,
createdAt DATE NOT NULL,
bookingDateTime DATE NOT NULL,
petId NUMBER NOT NULL,
roomId VARCHAR(4) NOT NULL,
staffId NUMBER NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (petId) REFERENCES Pets (id),
FOREIGN KEY (roomId) REFERENCES Rooms (id),
FOREIGN KEY (staffId) REFERENCES Staffs (id)
);

Here is the sql query.

SELECT S.id Staff_ID,S.name Staff_Name,COUNT(A.id) TOTAL_COUNTS
FROM Staffs S, Appointments A
WHERE S.id = A.staffId
GROUP BY S.id,S.name
ORDER BY TOTAL_COUNTS DESC;

CodePudding user response：

Please use left join instead of the inner join:

Here is the query that will give you the required output:

SELECT 
    S.id as Staff_ID,
    S.name Staff_Name,
    COUNT(A.id) TOTAL_COUNTS
FROM Staffs S left join Appointments A
    ON S.id = A.staffId
GROUP BY S.id,S.name
ORDER BY TOTAL_COUNTS DESC;