complex query

Question

Post reply

complex query

smknox

Hall of Fame

Points: 3275
More actions
April 12, 2008 at 1:23 pm

#189022

In the following select statement, the Appointment and Ailment tables are connected via the AppointmentAilment association table. An appointment could have several ailments associated with it. In this case, I want to be certain that only one Ailment record is selected, so I request the min AppointmentAilmentID record
SELECT f.AppointmentID,t.MedicalCodeID,
f.AilmentID, f.AppointmentAilmentID
FROM
dbo.AppointmentAilment f
INNER JOIN
dbo.Ailment t
ON t.AilmentID=f.AilmentID
WHERE
f.AppointmentAilmentID =
(
SELECT Min(b.AppointmentAilmentID)
FROM
dbo.AppointmentAilment b
WHERE
b.IsPrimary=1
AND
b.AppointmentID=a.AppointmentID

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

smknox Hall of Fame Points: 3275 More actions · Answer 1

nevermind : I found the problem and it's fixed.

Sam

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 2

So, what was the problem and how did you fix it?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

From looking at the OP, I would guess a last closing paranthesis...

N 56°04'39.16"
E 12°55'05.25"

smknox Hall of Fame Points: 3275 More actions · Answer 4

kinda embarrassing but it was the

b.AppointmentID=a.AppointmentID

in the last line. I changed it to

b.AppointmentID=f.AppointmentID

the "f" alias referred back to the AppointmentAilment table. The "a" alias was referring back to another copy of AppointmentID earlier in the statement (I did not post entire query).

Sam

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 5

Heh... I've made many such a mistake... thanks for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)