CODEWITHSUNDEEP
sqlms-accessjoinleft-join
Ben Elgar
Ben Elgar

Reputation: 785

Selecting Records in Joined Tables

I have two tables in MS Access. One an Appointment table and one a Slot table. Like so: Tables

Now I would like to select every record from the slot table and if there is a record with TeacherID = "CR" in the appointment table I would like it to display the StudentID.

This is the SQL I have so far:

SELECT Slot.SlotNumber, Slot.SlotTime, Appointment.TeacherID
FROM Slot LEFT JOIN Appointment ON Slot.SlotNumber = Appointment.SlotNumber
WHERE Appointment.TeacherID="CR"

However this only selects the slots where there is a record in the Appointment table. Any suggestions? Thanks in advance!

Upvotes: 0

Views: 47

Answers (1)

Arion
Arion

Reputation: 31239

If I understand you correct. You do not want the where statement. Instead put it in the LEFT JOIN. Like this:

SELECT 
  Slot.SlotNumber, 
  Slot.SlotTime, 
  Appointment.TeacherID
FROM 
  Slot 
  LEFT JOIN Appointment 
    ON (Slot.SlotNumber = Appointment.SlotNumber
    AND Appointment.TeacherID="CR")

Upvotes: 1

Related Questions