Hi i am trying to get a mysql query working and need some help. I have 2 tables (staff, booking).
Staff table contains staff details
| StaffID | First_name | Last_name |
|---|---|---|
| 1 | John | Doe |
| 2 | Mary | Doe |
Booking table contains
| BookingID | StaffID | Status |
|---|---|---|
| 1 | 1 | cancelled |
| 2 | 1 | cancelled |
| 3 | 1 | confirmed |
| 4 | 2 | cancelled |
| 5 | 1 | confirmed |
I would like to get the count of confirmed/cancelled bookings for each staff but is having trouble getting it to work. My current query that I'm using is
Select staff.StaffID, staff.First_name, staff.Last_name, sum(booking.Status LIKE '$status') as Status
from staff, booking
where staff.StaffID = booking.ConvenerID
group by staff.StaffID
$status being cancelled or confirmed. With this query I'm only able to display a row if there is a status value, if a staff have no cancelled it does not display the row for that staff. How can I get the row to display even if the count of $status is 0.
CodePudding user response：
You need a LEFT join of the tables:
SELECT s.StaffID, s.First_name, s.Last_name,
COALESCE(SUM(b.Status = ?), 0) AS counter
FROM staff s LEFT JOIN booking b
ON s.StaffID = b.StaffID
GROUP BY s.StaffID;
Replace ? with the status value that you want (there is no reason to use the operator LIKE because a simple = works fine).