[RESOLVED] NULLIF or something else?

[damienmcd]

Hi Guys,

I am making a booking system and am looking to figure out how to calculate the available seats on the td_timetables table below by subtracting the number of booked seats from the td_bookings table.

The table structure is as follows:
TD_TIMETABLES:
tim_id(PK)
tim_num_seats

TD_BOOKINGS:
boo_id(PK)
boo_tim_id(FK)
boo_num_seats

The td_timetables table contains the amount of seats available on a train and the td_bookings contains the number of seats already booked.

The problem is that if there are no seats booked, or no bookings for that particular timetable, then the result of the query below is NULL.

SELECT ( td_timetables.tim_num_seats - ( SELECT SUM( NULLIF( td_bookings.boo_num_seats, 0 ) ) ) ) AS available_seats
FROM `td_timetables`, `td_bookings`
WHERE td_timetables.tim_id = td_bookings.boo_tim_id
AND td_timetables.tim_id = 9

What i need is that if there is no booking record for a particular timetable, then it returns the original number of seats from the td_timetables table.

I thought this could be done by using NULLIF for the number of booked seats and setting the value to 0 if it is NULL, however this does not return what I am looking for.

Any help here would be greatly appreciated.
Thanks in advance!

[LordShryku]

Try IFNULL instead. Also, not so sure you need the extra SELECT in there. Just sum(ifnull(td_bookings.boo_num_seats, 0)) should work.

[damienmcd]

Thanks so much for the reply!!

It was almost perfect but I have the solution now.

 SELECT td_timetables.tim_num_seats - ( IFNULL( sum( td_bookings.boo_num_seats ) , 0 ) ) AS available_seats
FROM `td_timetables` , `td_bookings`
WHERE td_timetables.tim_id = td_bookings.boo_tim_id
AND td_bookings.boo_tim_id =19 

For anyone else looking for a similar solution this is it. If the result from the SUM function was also NULL then that needs to be wrapped in another IFNULL.

Thanks for the help!
It is much appreciated!