CODEWITHSUNDEEP
mysqlsqlcount
Peter Roche
Peter Roche

Reputation: 335

Returning rows with 0 count

Attempting to return the number of staff that has been booked to work a specific event by using a count function on tblEmployeeBooking ; This value will be used as a comparison against the number of staff required from tblEvent.

Currently, the below statement doesn't return any tblEvent records which have a zero count within tblEmployeeBooking

SELECT e.eventID
     , e.staffQuantity
     , Count(b.eventID) AS CountOfeventID
  FROM tblEmployeeBooking b
  LEFT 
  JOIN tblEvent e 
    ON b.eventID = e.eventID
GROUP 
    BY e.eventID
     , e.staffQuantity
     , b.cancelled
HAVING (((b.cancelled)=0));

Upvotes: 0

Views: 34

Answers (1)

Barmar
Barmar

Reputation: 780879

You either need to use RIGHT JOIN or switch the order of the tables in your LEFT JOIN. When you use LEFT JOIN, it selects all matching rows from the left table, it's the right table that's allowed to have non-matches in the ON condition.

Also, you need to move any conditions on the table that could have missing rows into the ON clause. Testing them in WHERE or HAVING will filter out those rows because columns are all NULL when there's no match, and that doesn't = 0.

And you shouldn't use any columns from the child table in GROUP BY, because when there's no match those columns will all be NULL, and they'll group together. In this case, there's no point in grouping by tblEmployeeBooking.cancelled because you're only selecting rows where cancelled = 0, so they'll all be in the same group anyway.

So it should be:

SELECT tblEvent.eventID, tblEvent.staffQuantity, Count(tblEmployeeBooking.eventID) AS CountOfeventID
FROM tblEvent
LEFT JOIN tblEmployeeBooking ON tblEmployeeBooking.eventID = tblEvent.eventID AND tblEmployeeBooking.cancelled = 0
GROUP BY tblEvent.eventID, tblEvent.staffQuantity

Upvotes: 1

Related Questions