Table Relationship Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Table Relationship Problem

Author

Topic

sross81
Posting Yak Master

228 Posts

Posted - 2008-10-29 : 17:38:56

Hello,

I have a problem when it comes to relating two tables. Simplied versions of my tables are as follows.

Person
person_id
1

Patient_Encounter (each person can have multiple enc_id's)
person_id, enc_id
1, a1
1, a2
1, a3

patient_medication (each person on a specific encounter can have multiple problems)
uniq_id, person_id,enc_id, problem
1,1,a1,sick
2,1,a1,problem

I have a query that is linked to a crystal report and all the parameters get passed there but the user chooses a person_id and a date (which relates to an enc_id) So for example they would choose person 1, and enc a1 and it would return 2 rows because for that person on that encounter there are two problems assocatied with it. I have a query that will perform that concatenation into one row because what I want to see is the person_id,enc_id, and a list of their problems.......

BUT I don't understand how to handle the uniq_id field in the patient medication table since each entry is unique I keep getting two rows no matter what I do. I tried doing max(uniq_id) but its not allowed with that data type. I was wondering if I need to have two separate queries. The first query would retrieve the values and then I can dump them into another query that will create the list that way the uniq_id cannot interfere???

Below is the code I have already set up:

select max(b.create_timestamp),b.enc_id,b.person_id,LTRIM(a.person_nbr) as AcctNbr,
a.first_name + ' ' + a.last_name as fullname,
a.date_of_birth, max(b.rx_refills) as refills,
e.first_name + ' ' + e.last_name as provname,
e.other_lic_id,f.address_line_1,f.city,f.state,f.zip,f.phone,f.fax,
f.location_name,
coalesce(left(dl.desclist,len(dl.desclist)-1),'') as problem
from person a
join patient_medication b on a.person_id = b.person_id
join patient_encounter c on b.enc_id = c.enc_id and a.person_id = c.person_id
join fdb_medication d on b.ndc_id = d.ndc_id
join provider_mstr e on b.provider_id = e.provider_id
join location_mstr f on b.location_id = f.location_id
outer apply (select description + ','
from diagnosis_code_mstr
where diagnosis_code_id=b.diagnosis_code_id
for xml path(''))dl(desclist)
where d.gcn = '94200' and b.date_stopped = ' ' and a.person_nbr = 77622
group by b.enc_id,dl.desclist,b.person_id,a.person_nbr,a.first_name,a.last_name,
a.date_of_birth,
e.first_name,e.last_name,e.other_lic_id,f.address_line_1,f.city,f.state,f.zip,
f.phone,f.fax,f.location_name

Thanks in Advance!
Sherri

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-30 : 04:08:06

you sample data can be linked like this to get single value

select pe.person_id, pe.enc_id,
left(pl.problist,len(pl.problist)-1)
from Person p
inner join Patient_Encounter pe
on pe.person_id=p.person_id
cross apply (select problem + ','
             from patient_medication
             where person_id=p.person_id
             and   enc_id=pe.enc_id
             for xml path(''))pl(problist)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources