Query with embedded SELECT

This is a request for help with a SELECT within SELECT. The following return
s
open occurances (no END_DATE) and selects the associated appointment with th
e
highest APPT_ID. I want to imbed another SELECT that will 1) read back to th
e
Appointment table (joining back on the APPT_ID) and return the APPT_DATE for
this appointment. 2) I want to JOIN to a table called Resource AS r on a
column in the Appointment table called a.RESOURCE_ID and get r. RESOURCE_NAM
E
from the resource table. Here is the original SELECT:
SELECT o.OCCURANCE_DESC AS CaseID, p.home_phone AS phone, RTRIM(p.first_name
+ ' ' + p.middle_name) + ' ' + p.last_name AS Patient, o.NOTE AS Comment,
MAX(a.APPT_ID) AS LastAppt FROM Occurance o INNER JOIN Patient p ON
o.CLIENT_ID = p.PatUniqueID JOIN Appointment a ON o.OCCURANCE_ID =
a.OCCURANCE_ID WHERE ISDATE(END_DATE) = 0 AND a.Status < 200 GROUP BY
o.OCCURANCE_DESC, p.home_phone, RTRIM(p.first_name + ' ' + p.middle_name) +
' ' + p.last_name, o.NOTE, o.OCCURANCE_ID ;
Thank you very muchrichardb
Please post DDL+ sample data + expected result. It's hard to suggest
something.
"richardb" <richardb@.discussions.microsoft.com> wrote in message
news:4D7B74D9-AD1C-40BB-94FF-EC18FF764920@.microsoft.com...
> This is a request for help with a SELECT within SELECT. The following
returns
> open occurances (no END_DATE) and selects the associated appointment with
the
> highest APPT_ID. I want to imbed another SELECT that will 1) read back to
the
> Appointment table (joining back on the APPT_ID) and return the APPT_DATE
for
> this appointment. 2) I want to JOIN to a table called Resource AS r on a
> column in the Appointment table called a.RESOURCE_ID and get r.
RESOURCE_NAME
> from the resource table. Here is the original SELECT:
> SELECT o.OCCURANCE_DESC AS CaseID, p.home_phone AS phone,
RTRIM(p.first_name
> + ' ' + p.middle_name) + ' ' + p.last_name AS Patient, o.NOTE AS Comment,
> MAX(a.APPT_ID) AS LastAppt FROM Occurance o INNER JOIN Patient p ON
> o.CLIENT_ID = p.PatUniqueID JOIN Appointment a ON o.OCCURANCE_ID =
> a.OCCURANCE_ID WHERE ISDATE(END_DATE) = 0 AND a.Status < 200 GROUP BY
> o.OCCURANCE_DESC, p.home_phone, RTRIM(p.first_name + ' ' + p.middle_name)
+
> ' ' + p.last_name, o.NOTE, o.OCCURANCE_ID ;
> Thank you very much|||I don't know what "post DDL+ sample data" means? Let me try to simplify the
request. Let's say I am selecting from a table of appointments the latest
appointment of each type for each patient as follows
SELECT p.PATIENT_NAME, a.APPT_TYPE, a.MAX(APPT_ID) AS LastestAppt FROM
Appointments JOIN Patients p ON a.PATIENT_ID = p.PATIENT_ID WHERE STATUS <
200 GROUP BY PATIENT_NAME, APPT_TYPE.
This would give:
PATIENT_NAME APPT_TYPE LastestAppt
-- -- --
JONES 90800 43
SMITH 81000 35
etc.
Each appointment row includes a code for the doctor. However, I did not want
to group on the doctor initially, because I only want one most recent
appointment for each patient. Now I want to know who is the doctor for that
appointment, so I think I need a second imbeded SELECT to go back into the
Appointment file and pick off the doctor's ID, resulting in:
PATIENT_NAME APPT_TYPE LastestAppt Doctor
-- -- -- --
JONES 90800 43 RMB
SMITH 81000 35 JDB
etc.
I cannot figure out how to do that and would appreciate help with an example
.
"Uri Dimant" wrote:

> richardb
> Please post DDL+ sample data + expected result. It's hard to suggest
> something.
>
>
> "richardb" <richardb@.discussions.microsoft.com> wrote in message
> news:4D7B74D9-AD1C-40BB-94FF-EC18FF764920@.microsoft.com...
> returns
> the
> the
> for
> RESOURCE_NAME
> RTRIM(p.first_name
> +
>
>|||Richardb
DDL means to post actual a table structure with some data.
CREATE TABLE xxx
(
col1 INT,
col2 INT,
blala...
)
INSERT INTO xxx VALUES ('data',data'.....)
Based on your narrotive I guess you need the following
SELECT p.PATIENT_NAME, a.APPT_TYPE,
a.MAX(APPT_ID) AS LastestAppt,D.Doctorid
FROM Appointments JOIN Patients p ON a.PATIENT_ID = p.PATIENT_ID JOIN
(SELECT max(doctoreID) Doctorid FROM
FROM 'SomeTable' GROUP BY Somthing) D ON
D.doctoreID=AnotherTable.Doctorid
WHERE STATUS <
200 GROUP BY PATIENT_NAME, APPT_TYPE.
"richardb" <richardb@.discussions.microsoft.com> wrote in message
news:8A2292C7-A997-47BA-A0A0-5173DDA61C26@.microsoft.com...
> I don't know what "post DDL+ sample data" means? Let me try to simplify
the
> request. Let's say I am selecting from a table of appointments the latest
> appointment of each type for each patient as follows
> SELECT p.PATIENT_NAME, a.APPT_TYPE, a.MAX(APPT_ID) AS LastestAppt FROM
> Appointments JOIN Patients p ON a.PATIENT_ID = p.PATIENT_ID WHERE STATUS <
> 200 GROUP BY PATIENT_NAME, APPT_TYPE.
> This would give:
> PATIENT_NAME APPT_TYPE LastestAppt
> -- -- --
> JONES 90800 43
> SMITH 81000 35
> etc.
> Each appointment row includes a code for the doctor. However, I did not
want
> to group on the doctor initially, because I only want one most recent
> appointment for each patient. Now I want to know who is the doctor for
that
> appointment, so I think I need a second imbeded SELECT to go back into the
> Appointment file and pick off the doctor's ID, resulting in:
> PATIENT_NAME APPT_TYPE LastestAppt Doctor
> -- -- -- --
> JONES 90800 43 RMB
> SMITH 81000 35 JDB
> etc.
> I cannot figure out how to do that and would appreciate help with an
example.
>
> "Uri Dimant" wrote:
>
with
to
APPT_DATE
a
Comment,
p.middle_name)sql