self join solution

Last Post 10 Dec 2008 12:57 PM by SwePeso. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

10 Dec 2008 06:29 AM
I have a tsql puzzler looking for some direction on best approach
here is my current sql

select distinct a.patientid,a.allergenname , as reaction
from allergy a
left outer join allergyreaction r
on r.allergyid =
inner join reaction rr on = r.reactionid
where sendtopharmacy = 'true'
order by a.patientid,a.allergenname

and here are the results

88 Avar Cleanser Anemia
88 Avar Cleanser Wheezing
88 Avena Sativa Other
88 Avena Sativa Rash
88 Avena Sativa Shock
224 Loosen Nausea

What I need is a record with patientid, allergy and then all the reactions as one row. There could be from 1-6 reactions.
New Member
New Member

10 Dec 2008 12:57 PM

Acceptable Use Policy