Multiple table 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

SQL Server 2000 Forums

SQL Server Development (2000)

Multiple table join.

Author

Topic

macca
Posting Yak Master

146 Posts

Posted - 2005-11-08 : 06:53:21

I have five tables in my database that are, Request, Engineer, Status, Area, Mentor. The main table is Request, all four other tables are joined to the request table but not to each other. Each table is connected to the Request table with a key, Engineer table connects using EngineerID, Status connects using StatusID and same for the other tables. Each Request in the Request table has a unique RequestID.

What I want to do is select * from the Request table where RequestID = 'Some ID'. I want all the values of the ID's in each table so obviously I have to do four joins.

Anyone know how I could do this.

Thanks,

macca

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-08 : 07:06:28

Try this

Select Columns from Engineer E where exists(select * from the Request where RequestID =E.RequestID and RequestID = 'Some ID')

Union all

Select Columns from Status S where exists(select * from the Request where StatusId=S.StatusID and RequestID = 'Some ID')

Union all
.
.

Madhivanan

Failing to plan is Planning to fail

macca
Posting Yak Master

146 Posts

Posted - 2005-11-08 : 07:37:44

Thanks for the reply Madhivnan.

What I am trying to do is something like:

SELECT R.RequestID, R.RequestName, S.StatusName, E.EngineerName
FROM Status AS S
JOIN Request As R
ON S.StatusID = R.StatusID
LEFT OUTER JOIN Engineer As E
ON E.SignedId = R.SignedId
WHERE O.OrderNum = 'Some number'

The above code doew the select joining Engineer and Ststus tables to Request table.
I am not sure if this is correct code.
I am also unsure how to do select while joing all 4 tables to request.

Do you know how to do using above code type?

macca

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-11-08 : 09:10:37

One of us is missing something obvious. Why can't you just do this:

SELECT	Request.RequestID,
	Request.RequestName,
	Status.StatusName,
	Engineer.EngineerName,
	etc...
from	Request
	left outer join Engineer on Request.EngineerID = Engineer.EngineerID
	left outer join Status on Request.StatusID = Status.StatusID
	left outer join Area on Request.AreaID = Area.AreaID
	left outer join Mentor on Request.MentorID = Mentor.MentorID

...and what is O.OrderNum? You have no table aliased as "O". Exactly why I hate lazy and gratuitous aliasing - readability and debugging get flushed down the toilet.

macca
Posting Yak Master

146 Posts

Posted - 2005-11-08 : 09:34:19

do i need to have left outer join on all of them or will just JOIN suffice?

macca

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-11-08 : 10:49:52

Left outer join ensures you will get a record back for each request, regardless of whether there are associated records in the subtables. If you are SURE that there is a record in each subtable for each request, then you do not need LEFT OUTER JOINs, but I would leave it in there anyway.

macca
Posting Yak Master

146 Posts

Posted - 2005-11-08 : 11:00:01

Thanks Blindman.

macca

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources