SQLTeam.com | Weblogs | Forums

Need help on a "simple" query


#1
The above query gives me this result set...
[code]ID4     Locationn4
16816   Board & Care
16814   Home
[/code]
I'm trying to include the above query in this new one but my location comes up as null...I've struggled with this for over an hour now. I must be missing something simple here...
[code]SELECT Patient.ID, Patient.LastName, Patient.FirstName, Patient.MRNum, PatientLocation.Location4, PatientLocation.ID4
FROM  Patient LEFT OUTER JOIN
      PatientLocation ON Patient.ID = (SELECT TOP(1) ID4 FROM PatientLocation WHERE Patientid4 = 9614 ORDER BY ID4)
WHERE Patient.ID = 9614[/code]

The result set from second query...
[code]ID    LastName    FirstName    MRNum    Location4   ID4
9614  Bates       Kathy        3336     NULL        NULL
[/code]

The above query gives me this result set...

ID4 Locationn4 16816 Board & Care 16814 Home
I'm trying to include the above query in this new one but my location comes up as null...I've struggled with this for over an hour now. I must be missing something simple here...

SELECT Patient.ID, Patient.LastName, Patient.FirstName, Patient.MRNum, PatientLocation.Location4, PatientLocation.ID4 FROM Patient LEFT OUTER JOIN PatientLocation ON Patient.ID = (SELECT TOP(1) ID4 FROM PatientLocation WHERE Patientid4 = 9614 ORDER BY ID4) WHERE Patient.ID = 9614

The result set from second query...

ID LastName FirstName MRNum Location4 ID4 9614 Bates Kathy 3336 NULL NULL


#2

Your join condition is wrong. ID4 != Patient.ID according to the data you provided. I think it should be this:

SELECT Patient.ID, Patient.LastName, Patient.FirstName, Patient.MRNum, PatientLocation.Location4, PatientLocation.ID4
FROM Patient
LEFT OUTER JOIN PatientLocation
ON Patient.ID = PatientLocation.Patientid4
WHERE Patient.ID = 9614


#3

Sorry ...I wasn't clear in my first post.

My goal is to only include the latest location in my second query. The result set should look like this...

ID    LastName    FirstName    MRNum    Location4   ID4
9614  Bates       Kathy        3336     16815       Board & Care

#4

SELECT TOP 1 Patient.ID, Patient.LastName, Patient.FirstName, Patient.MRNum, PatientLocation.Location4, PatientLocation.ID4
FROM Patient
LEFT OUTER JOIN PatientLocation
ON Patient.ID = PatientLocation.Patientid4
WHERE Patient.ID = 9614
ORDER BY PatientLocation.Location4


#5

Ahhhh yes! ....Thank you thank you! :grinning: