Selecting between tables

Mohamed Shehab

Dear guys:
               i'm developing a booking system for a clinic, so my problem is that i want to display available doctor shift which are not already reserved, the patient check the availability of a certain doctor in specific date and shift.
i have 2 tables Booking (ID, DocID, shift_id, Date)and Shift table (ID, name, DocID)
my SQL selecting query which r not working is:

SELECT        Shift.ID, Shift.name, Shift.DocID
FROM            Shift INNER JOIN
                         Booking ON Shift.ID = Booking.shift_id
WHERE        (Shift.DocID = @DoctorID) AND (Booking.DocID <> @DoctorID) AND (Booking.shift_id <> @ShiftID) AND (Booking.Date <> @VisitDate)

so please any ideas???

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Mohamed Shehab14y ago
many thanx Javeed, i found the right select statement : SELECT Shift.* FROM Shift LEFT OUTER JOIN Booking ON Shift.ID = Booking.shift_id AND Shift.DocID = Booking.DocID WHERE (Booking.ID IS NULL) OR (Booking.Date <> @VDate)
Javeed M Shaikh14y ago
Hi Shehab,

Please provide some sample data for both tables and the information you are passing in the parameters (alsoif you can provide expected output based on the data)