Hi Guys.

I am looking for what will be really simple for someone here so any help is greatly appreciated.

I have 2 tables, td_timetables and td_bookings.
TD_TIMETABLES:
tim_id
tim_time
tim_rou_id
tim_num_seats
tim_duration
tim_season

TD_BOOKINGS:
boo_id
boo_tim_id
boo_cus_id
boo_date
boo_num_seats
boo_cancelled

I am trying to get the number of available seats from TD_TIMETABLES - TD_BOOKINGS. I have this working but it only returns the available seats for TD_TIMETABLES entries that have at least one corresponding TD_BOOKINGS entry.
I need to return the results for all entries in TD_TIMETABLES, regardless of whether there is a corresponding booking for that entry.

Here is what I have currently:

-- find all available seats per route for a certain day

SELECT 
td_timetables.tim_time
, td_timetables.tim_num_seats
, IFNULL( (
td_timetables.tim_num_seats - IFNULL( SUM( td_bookings.boo_num_seats ) , 0 ) ) , td_timetables.tim_num_seats
) AS available_seats

FROM `td_bookings` , `td_timetables` 

WHERE td_timetables.tim_id = td_bookings.boo_tim_id
AND td_bookings.boo_date = '2010-01-05'
AND td_bookings.boo_cancelled =0
AND td_timetables.tim_rou_id =1
AND td_timetables.tim_season =1

GROUP BY td_timetables.tim_id

The current output is:

tim_time 	tim_num_seats 	available_seats
0900 	100 	96
1100 	100 	79
1300 	100 	88
1700 	100 	89

But I would like to see:

tim_time 	tim_num_seats 	available_seats
0900 	100 	96
1100 	100 	79
1300 	100 	88
1500 	100 	100
1700 	100 	89
1900 	100 	100
2100 	100 	100
2300 	100 	100

The situation is that if there is no booking for a timetable entry that it will return the full capacity of the train, TD_TIMETABLES.tim_num_seats. Can anyone help me with this?
I have tried a UNION but it is returning results with NULLS, which is of no use to me.

Any help is greatly appreciated!
Thanks in advance.

    As has already been pointed out: no UNION need but a JOIN. You actually have a JOIN in your query already:

    FROM `td_bookings` , `td_timetables` 

WHERE td_timetables.tim_id = td_bookings.boo_tim_id

    You are actually performing a form of join here. It's called a comma join, and the other option an ANSI join would look like this:

    FROM `td_bookings` INNER JOIN `td_timetables` ON td_timetables.tim_id = td_bookings.boo_tim_id

    The latter approach has the advantage that it keeps joins and WHERE conditions separated which might help you to figure out what is going on more easily.

    to achieve what you want, you will need an OUTER JOIN, in your case a LEFT JOIN (because the td_timetables is on the left side in your sql). that can be achieved with both comma or ansi approach:

    ansi:

    FROM `td_bookings` LEFT JOIN `td_timetables` ON td_timetables.tim_id = td_bookings.boo_tim_id

    comma:

    FROM `td_bookings` , `td_timetables` 

WHERE (td_timetables.tim_id = td_bookings.boo_tim_id OR td_bookings.boo_tim_id IS NULL)

    hth

    Bjom

      Hi guys.

      Thanks for the replies. They have been very helpful in aiding my understanding of my situation and Joins in general. However, I am still getting the same results....

      I cannot get results to display if there is no booking for the corresponding td_timetables.tim_id int the td_bookings.boo_tim_id's

      Here is what I currently using:

      SELECT td_timetables.tim_time, td_timetables.tim_num_seats, IFNULL( (
td_timetables.tim_num_seats - IFNULL( SUM( td_bookings.boo_num_seats ) , 0 ) ) , td_timetables.tim_num_seats
) AS available_seats

FROM `td_timetables`

LEFT JOIN `td_bookings` ON td_timetables.tim_id = td_bookings.boo_tim_id

WHERE td_timetables.tim_rou_id =1
AND td_timetables.tim_season =1
AND td_bookings.boo_date = '2010-01-05'
AND td_bookings.boo_cancelled =0

GROUP BY td_timetables.tim_id

      I really appreciate your help to this point.
      Thanks again in advance 🙂

        Because whenever there is no related information in the joined table, all its columns are set to null, which means boo_date = '2010-01-05' is false, and the same goes for boo_cacelled. Changing those along these lines should do the trick

        AND (boo_date = '2010-01-05' OR boo_date IS NULL)

          Guys/Dolls,

          Thank you all so much. It is now working perfectly!
          You guys/dolls all rule 🙂

          Thanks again!

          Here's the final completed version for anyone else in the same situation:

          SELECT td_timetables.tim_time, td_timetables.tim_num_seats, IFNULL( (
td_timetables.tim_num_seats - IFNULL( SUM( td_bookings.boo_num_seats ) , 0 ) ) , td_timetables.tim_num_seats
) AS available_seats
FROM `td_timetables`
LEFT JOIN `td_bookings` ON td_timetables.tim_id = td_bookings.boo_tim_id
WHERE td_timetables.tim_rou_id =1
AND td_timetables.tim_season =1
AND (
td_bookings.boo_date = '2010-01-06'
OR td_bookings.boo_date IS NULL
)
GROUP BY td_timetables.tim_id
            Write a Reply...