Return 0 for SUM if no join was possible

[deneguil]

Hello, I am a bit stuck with designing a specific query. Basically I have a table to with objects called work units and another table for people to book hours on said work units. I want to be able to sum up all the hours booked for a work unit. So I have written this query :

SELECT SUM(Booking.time) AS total_hrs FROM Booking
INNER JOIN Work_Units ON Booking.wu_id = Work_Units.id

That works perfectly when there are bookings for a specific work unit, but when there is not (in the case the work unit was just created for example), the join cannot happen and I am missing a row. I need this row because this data is part of a bigger query where it is needed.

I tried to use a substistute for COALESCE by doing

SELECT Nz(SUM(Booking.time); 0)

but it didn't work, neither did

SELECT IIF(ISNULL(SUM(Booking.time)); 0; SUM(Booking.time))

Any ideas on how to implement that?

 

[Pat Hartman]

You need to use a left join rather than an inner join.

 

[deneguil]

I can't believe it was such a simple fix, it really has been a while since the last time I used different joins in SQL I am quite embarrassed I forgot about them. Nonetheless thank you very much!

 

[Pat Hartman]

You're quite welcome. Welcome btw.