joining 3 tables - 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 2000 Forums

Transact-SQL (2000)

joining 3 tables

Author

Topic

kholloway
Starting Member

3 Posts

Posted - 2004-06-28 : 14:50:54

Hello. I am new to SQL server. I have created our Intranet site with ASP. I need to figure out how to be able to create a search where the results will be pulled from 3 different tables. The search also needs to work if they search using any of the three fields ie. the user can search on a patient using either their patient number, social security and/or their name. Any help would be GREATLY appreciated!!! Thanks!

javamick
Starting Member

7 Posts

Posted - 2004-06-28 : 15:37:59

Maybe you could provide table structure?

Thanks,
Mick

--
Micky McQuade
www.mcquade.com

kholloway
Starting Member

3 Posts

Posted - 2004-06-28 : 16:01:55

Here is the structure on the three tables I need to pull from:
PatientTable1
PatId(primaryKey)
PrefixId
Number
UserId
PatientTable2
SourceID
PatID(primaryKey)
add1
add3
BDate
Confidential
PatientTable3
PatientID(primaryKey)
VisitID(primaryKey)
DChargeDate
Reason
VisitType
VisitAcctNum

I can join the first two table but I don't know how to write the WHERE clause to make sure that the patientID is equal to the other PatientID fields in the other two tables. Thanks!

javamick
Starting Member

7 Posts

Posted - 2004-06-28 : 16:39:10

How about this:

SELECT <whateveryouneed>
FROM PatientTable1 PT1
INNER JOIN PatientTable2 PT2 ON PT1.PatientID = PT2.PatientID
INNER JOIN PatientTable2 PT3 ON PT1.PatientID = PT3.PatientID
WHERE <querystuff>

--
Micky McQuade
www.mcquade.com

javamick
Starting Member

7 Posts

Posted - 2004-06-28 : 16:39:43

oops

INNER JOIN PatientTable3 PT3 ON PT1.PatientID = PT3.PatientID

--
Micky McQuade
www.mcquade.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-28 : 16:40:17

Is PatID column the same as PatientID? If not, how is table3 related to the other 2 tables?

Tara

kholloway
Starting Member

3 Posts

Posted - 2004-06-29 : 13:39:13

Thanks McQuade. I will try what you posted and let you know.

Yes, PatId is the same as PatientID - sorry.