Joining Tables - One to Many - 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

General SQL Server Forums

New to SQL Server Programming

Joining Tables - One to Many

Author

Topic

josh2009
Yak Posting Veteran

81 Posts

Posted - 2009-08-25 : 14:15:34

I'm writing a query to display all equipment used for different procedures and having problems joining the tables. Here is my query -

select Demographics."Last_Name", Demographics."First_Name", Demographics."Patient_ID", Demographics."Date_of_Birth",
Event_Cath."Account_Number", Event_Cath."Date_of_Cath", Event_Cath."Room", Event_Cath.ss_event_cath_id,
Cath_Equipment."Item", Cath_Equipment."Quantity_Used",
Cath_Procedures."Procedure_Name"
from demographics
inner join event_cath
ON Event_Cath."SS_Patient_ID" = Demographics."SS_Patient_ID"
inner join cath_equipment
on event_cath.ss_event_cath_id = cath_equipment.ss_event_cath_id
join cath_procedures on cath_equipment.ss_event_cath_id = cath_procedures.ss_event_cath_id

Result set for ONE Patient -

AAAA 30cc IABP 7Fr Coronary Angiography
AAAA 100cc Visipaque 320mgI/ml Coronary Angiography
AAAA Left Heart Kit Coronary Angiography
AAAA Wire .035 Fixed J 145cm Coronary Angiography
AAAA 6Fr Sheath 10cm Coronary Angiography
AAAA Multi-pack JL4, JR4,Angl Pig 6Fr Coronary Angiography
AAAA 8Fr sheath 24cm Coronary Angiography
AAAA 8Fr 55cm Raabe sheath Coronary Angiography
AAAA 100cc Visipaque 320mgI/ml Coronary Angiography
AAAA 40cc IABP 8Fr Coronary Angiography
AAAA 30cc IABP 7Fr Left Heart Catheterization
AAAA 100cc Visipaque 320mgI/ml Left Heart Catheterization
AAAA Left Heart Kit Left Heart Catheterization
AAAA Wire .035 Fixed J 145cm Left Heart Catheterization
AAAA 6Fr Sheath 10cm Left Heart Catheterization
AAAA Multi-pack JL4, JR4,Angl Pig 6Fr Left Heart Catheterization
AAAA 8Fr sheath 24cm Left Heart Catheterization
AAAA 8Fr 55cm Raabe sheath Left Heart Catheterization
AAAA 100cc Visipaque 320mgI/ml Left Heart Catheterization
AAAA 40cc IABP 8Fr Left Heart Catheterization
AAAA 30cc IABP 7Fr Left Ventricular Angiogram
AAAA 100cc Visipaque 320mgI/ml Left Ventricular Angiogram
AAAA Left Heart Kit Left Ventricular Angiogram
AAAA Wire .035 Fixed J 145cm Left Ventricular Angiogram
AAAA 6Fr Sheath 10cm Left Ventricular Angiogram
AAAA Multi-pack JL4, JR4,Angl Pig 6Fr Left Ventricular Angiogram
AAAA 8Fr sheath 24cm Left Ventricular Angiogram
AAAA 8Fr 55cm Raabe sheath Left Ventricular Angiogram
AAAA 100cc Visipaque 320mgI/ml Left Ventricular Angiogram
AAAA 40cc IABP 8Fr Left Ventricular Angiogram

This is just a partial result set. As you can see, for this patient, rows are repeated for every item depending on the number of procedures done for this patient. And I can't find any other key I can use for a more solid join. Any help will be greatly appreciated. Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-25 : 18:23:53

So how would the "correct" output look like?

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources