QUERING Multiple tables using JOINS

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 2000 Forums

Transact-SQL (2000)

QUERING Multiple tables using JOINS

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-04-29 : 08:05:22

Ashwin writes "I have three tables (Patient, Study, and Images). The Study table has a foreign key linking it to the Patient table. And the Image table has a foreign key linking it to the Study table.

Hence a Patient has many Studies and a Study has many images. But a study has only one patient and an image belongs to only one study,

I want to return a recordset after the user specifies some search criteria.

For exmaple search criteria :
Patient Name (from patient table) = "Ashwin"
Study Date (from study table) = "2003-10-19"

must return all Patient table data, the corresponding Study table data for that patient (matching the date) as well as the corresponding Image table data for that study. I tried using Joins with unexpected results

Please help"

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-29 : 08:19:23

It would be easier with some dml.

SELECT A.*, B.*, C.*
FROM
Study A
LEFT JOIN Patient B ON A.PatientID = B.PatientID
LEFT JOIN Images C ON A.ImageID = B.ImageID
WHERE
A.StudyDate = '20031019' AND
B.PatientName = 'Ashwin'

Use ISO format for dates it's not installation dependent.
If you only want records that have all three pieces use an inner join.
You should rename your tables to Patients, Studies.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources