self join solution

Last Post 10 Dec 2008 12:57 PM by SwePeso. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
timcronin
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 ,rr.name as reaction
from allergy a
left outer join allergyreaction r
on r.allergyid = a.id
inner join reaction rr on rr.id = 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.
SwePeso
New Member
New Member

--
10 Dec 2008 12:57 PM


Acceptable Use Policy
---