Full outer join

I have the following senario at work
Table 1 - Visits with columns Visit_id*, Visit_description

Table 2 - Patients with columns pat_id*, event_date, visit_ID * primary keys

the visit_id column in patients is a foreign key to the Visits table. Patients are require to make say ten visits to the Dr. office. But some patients do not make all the visits.

So if I use a full outer join as follows:

Select P.pat_id, P.event_Date, V.Visit_description FROM visits V full outer join patients P on V.visit_id = P.visit_ID order BY P.pat_id

I should pick up visits that are never completed by a patients. However some patients have completed all visits so there is always a reference for each visit_id in the patients table.

I am trying to get a result set for all patients and their visits including the visits the they may have missed (i.e there is no record of the visit in the patients table.) because some patients have completed all vistis using a full outer join will not tell me if a patient has missed a visit, other than looking at the sequence of the visit ID.

If there is away to include the visit_id's for a given patient that has not completed the visit then please let me know.

Any help or suggestions are welcome.

Thanks
Jawahar Received on Thu Aug 25 2005 - 04:02:22 CDT