I am creating a stored procedure. I want to merge data from 3 tables.

1. schedule_rohan = contains hours available in the schedule according
to day
2. appointments_rohan = contains appointments
3. patients_rohan = contains patients

For example, I have the following as my select statement:

SELECT *
FROM
   appointments_rohan
   JOIN schedule_rohan ON
appointments_rohan.st_time=schedule_rohan.hour
   JOIN patients_rohan ON
appointments_rohan.name=patients_rohan.NoDossier
   WHERE appointments_rohan.cal_date = '5/19/2004' AND
schedule_rohan.day = 4
ORDER BY st_time ASC

It only produces a data set by matching the available hours from the
schedule for that day of the week to the appointment (st_time) for that
date in the appointments table.

How can I return a data set that matches the hours in the schedule for
that day, but also shows a null value for hours that don't have an
appointment in the appointments table (appointments_rohan)?

Thanks

Hi Karagias
Well this one of the most common mistake people make.
If you look at the query more closely you will observe that you are
filtering your result set by a where clause that says that the appointment
date shd be a specific value "WHERE appointments_rohan.cal_date = '5/19/2004'
" this means that date cannot be null in the result set. (which is what your
requirements seems to be)

If your requirement is to output null in the date then you cannot use your
where clause like that.

There are two ways you can write your query to get what you want
1.
SELECT *
FROM
   appointments_rohan
   JOIN schedule_rohan ON
appointments_rohan.st_time=schedule_rohan.hour
   JOIN patients_rohan ON
appointments_rohan.name=patients_rohan.NoDossier
   WHERE (appointments_rohan.cal_date = '5/19/2004' OR
appointments_rohan.cal_date is null )AND
schedule_rohan.day = 4
ORDER BY st_time ASC

2.
THe second way could be to alter the way you are joining tables. if you
wanna include all records from the hours table you can do a right outer join
(insead of an inner join) on the joining column and placing a condition of 
"appointments_rohan.cal_date = '5/19/2004' " right there only as illustrated
below:

SELECT *
FROM
   appointments_rohan
                    Right Outer JOIN schedule_rohan
                             ON appointments_rohan.st_time=schedule_rohan.hour
                             and appointments_rohan.cal_date = '5/19/2004'
                   JOIN patients_rohan
                             ON
appointments_rohan.name=patients_rohan.NoDossier   WHERE schedule_rohan.day =
4
ORDER BY st_time ASC

One other thing i would advise you is to use alias for table names. It
really makes your code cleaner and easier for others to understand.
You might wanna double check the syntax on the above query and do the
required changes.


Also please check the way you are joining Patients_rohan table. It seems you
are doing an inner join. Double check if that is what you really want.

Hope this helps. Ortherwise post your ddl and a sample set of data and i am
sure someone will give you the exact query you shd be writing.

Abhishek

Show quoteHide quote

convert INT
A difficult Combining Rows problem
How to use OPENXML to retrieve detail lineitems
Rearrange column
Processes in Sysprocesses