Click here to Skip to main content
15,867,488 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Dear guys:

I am 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)

i want to exclude the reserved shifts, and display just available shifts for this doctors??????
Posted
Updated 29-Nov-11 5:21am
v3
Comments
RaisKazi 29-Nov-11 10:24am    
Edited: 1) Formatting 2) Added "pre", "code" tags.
[no name] 29-Nov-11 10:55am    
You have to do better than "not working". Are you getting results? Are they not what is expected?
day_hard 29-Nov-11 10:58am    
no results from my select query
#realJSOP 29-Nov-11 11:22am    
It might help if you show us the query you're using...

If the shift table contains all the shift, doing a NOT IN join /a>[^] should give you the results.
 
Share this answer
 
SQL
select * from Shift where ID not in (select shift_id from Booking) and DocID =<specify id=""> and Date = <specify date="">

</specify></specify>



let me know if any queries.

Mark as solution if its solved your answer
 
Share this answer
 
Thanx for your help, i found the right query which is:

SQL
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)
 
Share this answer
 
Comments
RaviRanjanKr 29-Nov-11 14:17pm    
A suggestion :- its not a good practice to post your feedback or comment as an answer. you can use have a question or Comment.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900