Inner Join - 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

Inner Join

Author

Topic

sql_dan
Starting Member

43 Posts

Posted - 2009-12-08 : 06:12:26

Hi All,
I have just begun using SQL Server 2005 to manage a SQL database from the backend and I have having trouble with some joins.

I am trying to connect 3 tables: 'patients' 'theatre' and 'master'. I have been successful in creating a join between 'patients' and 'theatre' using the following code:

select
theatre.date as "Date Of Operation",
theatre.opsess as "Theatre",
theatre.[time] as "Time of Operation",
theatre.surgeon1 as "Surgeon",
theatre.anaes1 as "Anaesthetist",
dbo.proper (patient.[titl] + ' ' + patient.[fname]) + ' ' + upper(patient.[sname]) as "Patient",
patient.sex as "Sex",
theatre.mainproc as "Code",
theatre.note as "Instruments/Other"
from theatre
inner join patient on theatre.[key] = patient.[key]

But when I try to pull the name of the surgical procedure through from the 'master' table the 'theatre.mainproc' column goes blank.

The following was added into the above statement:

INSERTED IN SELECT master.definition as "Surgical Procedure"
INSERTED AT END inner join master on theatre.mainproc = master.definition

I don't see what is going wrong!!!

Any help would be amazing! The full statement going wrong is as follows:

select
theatre.date as "Date Of Operation",
theatre.opsess as "Theatre",
theatre.[time] as "Time of Operation",
theatre.surgeon1 as "Surgeon",
theatre.anaes1 as "Anaesthetist",
dbo.proper (patient.[titl] + ' ' + patient.[fname]) + ' ' + upper(patient.[sname]) as "Patient",
patient.sex as "Sex",
master.definition as "Surgical Procedure",
theatre.mainproc as "Code",
theatre.note as "Instruments/Other"
from theatre
inner join patient on theatre.[key] = patient.[key]
inner join master on theatre.mainproc = master.definition

If you cant sleep at night, its not the coffee its the bunk!

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2009-12-08 : 07:48:59

master database is same as systems master database or different ?

sql_dan
Starting Member

43 Posts

Posted - 2009-12-08 : 08:01:40

Same database difference table.
(Don't blame me I didn't build the thing I just try and work with it!)

If you cant sleep at night, its not the coffee its the bunk!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources