InnerJoin not giving null values 1

[clivehenderson]

Hi
Can anyone help me with this join problem please?
I have 2 tables and I'm trying o 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 which can have many tblbookings

tblxCourt has 2 fields
courtID slotID
1 1
1 2
1 3
tblxBookings has 3 fields
bookingID, bookedCourt and bookedSlot

1 1 1

SELECT tblxCourts.courtID, tblxCourts.courtSlot, tblxBookings.bookingID
FROM (tblxCourts
LEFT JOIN tblxBookings ON tblxBookings.bookedCourt=tblxCourts.courtID)


This returns 3 recordsas follows

Court ID Court Slot bookingID
1 1 1
1 2 1
1 3 1
Whereas I was expecting(wanting)
Court ID Court Slot bookingID
1 1 1
1 2 NULL
1 3 NULL


I hope this all makes sense and someone can spot where I've gone wrong. Any help appreciated.
Thanks
Clive

 

[r937]

your problem is right here --

ON tblxBookings.bookedCourt=tblxCourts.courtID

the row in tblxBookings matches all three rows in tblxCourts

delve more deeply into your tables to see why this isn't enough to generate the NULLs

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