CODEWITHSUNDEEP
sqldatabaseado
user1071629
user1071629

Reputation: 43

Selecting with filters

I'm developing a booking system for a clinic, so my aim 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)
Shift (ID, name, DocID)

MySQL selecting query which is 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???

Upvotes: 1

Views: 60

Answers (2)

user359040
user359040

Reputation:

Left join method:

SELECT s.* FROM Shift s
LEFT JOIN Booking b 
    ON s.id = b.shift_id
   AND s.DocID = b.DocID
   AND b.date = @visit_date
WHERE s.ID = @ShiftID and s.DocID = @DocID and b.id IS NULL

You could also write variants using NOT EXISTS or NOT IN clauses - the point is that you want shifts where there is no booking for that doctor, for that shift, on that day

Upvotes: 0

Marco
Marco

Reputation: 57593

You could try

SELECT s.* FROM Shift
LEFT JOIN Booking b 
    ON s.id = b.shift_id
   AND s.DocID = b.DocID
WHERE b.id IS NULL
   OR b.date <> visit_date

Upvotes: 1

Related Questions