self join solution

Last Post 10 Dec 2008 01:57 PM by SwePeso. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
timcronin
New Member
New Member

--
10 Dec 2008 07: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 01:57 PM
You are not authorized to post a reply.

Acceptable Use Policy