Left Join query kepps returning empty data 2

[clivehenderson]

Can anyone help me with this join problem please?
I have 2 tables and I'm trying to retrieve all the records from one table plus any related records from the second table returning null values if there are no matching records in the second table.
The LEFT Join sounds ideal to do this.

tblxCourts can have many tblbookings

tblxCourts has 2 fields which contain the available slots for any given date.
courtID slotID
1 1
1 2
1 3
tblxBookings has 4 fields and contains bookings for a court slot on a date
bookingID, bookedCourt and bookedSlot bookedDate

1 1 1 2009-01-10

So I've tried to use the following query to to retrieve all the slots (available and booked) for 10 January for Court number 1

SELECT tblxCourts.courtID, tblxCourts.courtSlot, tblxBookings.bookingID, tblxBookings.bookedDate
FROM (tblxCourts
LEFT JOIN tblxBookings ON tblxBookings.bookedSlot=tblxCourts.courtSlot)
WHERE tblxCourts.courtID=1
AND tblxBookings.bookedDate='2009-01-10'

It always returns an empty recordset.
I'm trying to get
Court Date Slot Booking ID
1 29-01-10 1 1
1 29-01-10 2 NULL
1 29-01-10 3 NULL

Guess I've really misunderstood this join and what it does!
Appreciate any help to overcome this problem.
Kind Regards
Clive

 

[towerbase]

Is it because your table tblxCourts has two fields called courtID and slotID and in your SELECT statement you are asking for tblxCourts.CourtSlot which doesn't exist?

Andrew
Hampshire, UK

 

[r937]

see also

http://www.tek-tips.com/viewthread.cfm?qid=1586539

i still think you're joining on the wrong columns

if you join just on the slot, you'll get your courts mixed up

also, a WHERE condition on a column of the right table in a LEFT OUTER JOIN will effectively turn it into an inner join -- move that condition into the ON clause



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon