perplexed by queries

I have a database of patients visiting a doctor.
Each patient has personal info in a PATIENT table
Each visit has visit info in a VISIT table, linked to the patient table
Each surgery performed has an entry in the SURGERY table, linked to the
visit table (because some visits have multiple surgeries, some have one, and
others have none)
Here's the problem: I need a query that shows each patient's most recent
visit to the doctor.
I made the query, but it would return all visits--but only when surgery was
performed. I guess the null value from a visit with no surgery made it not
return anything. How can I get it to show me only the most recent visit
record, regardless of if surgery was performed? is there some sql code I'm
missing?
Thanks,select *
from patient p join visit v on p.patientid=v.patientid
where v.visitid exists(select * from surgery s where s.visitid=visitid)
-oj
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:8D6AA643-BDE4-4BFA-8235-B77F3D46F3E6@.microsoft.com...
>I have a database of patients visiting a doctor.
> Each patient has personal info in a PATIENT table
> Each visit has visit info in a VISIT table, linked to the patient table
> Each surgery performed has an entry in the SURGERY table, linked to the
> visit table (because some visits have multiple surgeries, some have one,
> and
> others have none)
> Here's the problem: I need a query that shows each patient's most recent
> visit to the doctor.
> I made the query, but it would return all visits--but only when surgery
> was
> performed. I guess the null value from a visit with no surgery made it not
> return anything. How can I get it to show me only the most recent visit
> record, regardless of if surgery was performed? is there some sql code I'm
> missing?
> Thanks,|||PLEASE POST DDL and the query that you are currently using!
Without ANY idea of what your tables look like
or what the column names are,
this is of course a guess, but...
Select Patient, VisitDate
From Visits V Join Patients P
On P.PatientPK = V.PatientFK
Where V.VisitDate =
(Select Max(VisitDate)
From Visits
Where PatientFK = V.PatientFK)
"Scott" wrote:

> I have a database of patients visiting a doctor.
> Each patient has personal info in a PATIENT table
> Each visit has visit info in a VISIT table, linked to the patient table
> Each surgery performed has an entry in the SURGERY table, linked to the
> visit table (because some visits have multiple surgeries, some have one, a
nd
> others have none)
> Here's the problem: I need a query that shows each patient's most recent
> visit to the doctor.
> I made the query, but it would return all visits--but only when surgery wa
s
> performed. I guess the null value from a visit with no surgery made it not
> return anything. How can I get it to show me only the most recent visit
> record, regardless of if surgery was performed? is there some sql code I'm
> missing?
> Thanks,|||oops...forgot the 'last visit' bit...
select *
from patient p join visit v on p.patientid=v.patientid
where v.visitid exists(select * from surgery s where s.visitid=visitid)
and v.visitdate=(select max(v2.visitdate) from visit v2 where
v2.patientid=v.patientid)
-oj
"oj" <nospam_ojngo@.home.com> wrote in message
news:ex%231W3VRFHA.3628@.TK2MSFTNGP12.phx.gbl...
> select *
> from patient p join visit v on p.patientid=v.patientid
> where v.visitid exists(select * from surgery s where s.visitid=visitid)
> --
> -oj
>
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:8D6AA643-BDE4-4BFA-8235-B77F3D46F3E6@.microsoft.com...
>|||You wouldn't mention surgery if it was irrelevant, so my guess is
select
PATIENT.*, VISIT.*, SURGERY.*
from PATIENT join VISIT
on PATIENT.PatientID = VISIT.PatientID
left outer join SURGERY
on SURGERY.VisitID = VISIT.VisitID
where VISIT.VisitID = (
select top 1 VisitID
from VISIT
where VISIT.PatientID = PATIENT.PatientID
)
Of course this is all a guess, since you posted no details.
What you were probably missing was the outer join, so
patient visits are returned whether or not there was an
associated surgery.
Steve Kass
Drew University
Scott wrote:

>I have a database of patients visiting a doctor.
>Each patient has personal info in a PATIENT table
>Each visit has visit info in a VISIT table, linked to the patient table
>Each surgery performed has an entry in the SURGERY table, linked to the
>visit table (because some visits have multiple surgeries, some have one, an
d
>others have none)
>Here's the problem: I need a query that shows each patient's most recent
>visit to the doctor.
>I made the query, but it would return all visits--but only when surgery was
>performed. I guess the null value from a visit with no surgery made it not
>return anything. How can I get it to show me only the most recent visit
>record, regardless of if surgery was performed? is there some sql code I'm
>missing?
>Thanks,
>|||Oops - forgot the ORDER BY in the subquery:
select
PATIENT.*, VISIT.*, SURGERY.*
from PATIENT join VISIT
on PATIENT.PatientID = VISIT.PatientID
left outer join SURGERY
on SURGERY.VisitID = VISIT.VisitID
where VISIT.VisitID = (
select top 1 VisitID
from VISIT
where VISIT.PatientID = PATIENT.PatientID
order by VisitDate desc -- ***This line left out of previous reply***
)
Scott wrote:

>I have a database of patients visiting a doctor.
>Each patient has personal info in a PATIENT table
>Each visit has visit info in a VISIT table, linked to the patient table
>Each surgery performed has an entry in the SURGERY table, linked to the
>visit table (because some visits have multiple surgeries, some have one, an
d
>others have none)
>Here's the problem: I need a query that shows each patient's most recent
>visit to the doctor.
>I made the query, but it would return all visits--but only when surgery was
>performed. I guess the null value from a visit with no surgery made it not
>return anything. How can I get it to show me only the most recent visit
>record, regardless of if surgery was performed? is there some sql code I'm
>missing?
>Thanks,
>