multiple joins between tables

Forum|Forum|17 years ago
September 18, 2008
3 replies
681 views

Not sure that the title best describes this.

I've got two tables which I'll simplify here

t1 - building (BuildingCode, Reception, Porter, Manager)
A, 1, 3, 8
B, 2, 4, 9
C, 1, 4, 7

t2 - staff (ID, Name, Extn)
1, Dave, 3302
2, Chris, 3994
3, Claire, 3033
8, Simon, 3255

and i'd like to get a table out that looks like

A, Dave, 3302, Claire, 3033, Simon, 3255
B, Chris, 3994 etc.

select b.buildingCode, s.Name, s.Extn
from building b, staff s
where s.ID = b.Reception

would give me one set of details - how can i get all 3?

Do I need to do 3 queries - where s.ID = b.Reception, where s.ID = b.Porter and where s.ID = b.Manager and then join the 3 tables together or is there a more direct route?

Many thanks
Michael

Database access

This topic has been closed for replies.

M

Mi-ul-vmijiIAuthor

Known Participant

Now that I've had a chance to try the code I found that to use the results on the page I needed aliases in the select part as well - final code is

select t1.BuildingCode, r.Name as rname, r.Extn as rextn, p.Name as pname, p.Extn as pextn, m.Name as mname, m.Extn as mextn
from t1, t2 r, t2 p, t2 m
where t1.Reception = r.ID
and t1.Porter = p.ID
and t1.Manager = m.ID

-

-__cfSearching__-

Inspiring

You might also consider using ansi JOIN syntax instead.

SELECT t1.PKColumn, t1.OtherColumn
FROM table1 as t1
INNER JOIN table2 as t2 ON t1.PKColumn = t2.FKColumn

M

Mi-ul-vmijiIAuthor

Known Participant

Aha! Thanks for that - it's just what I was looking for. I'd tried select table.field as ??? but had never discovered being able to give tables multiple alias'

D

Daverms

Inspiring

Hi,

Can you please explain a bit more on what you are trying to acheive..

(What resultset you are getting now?.. and your expected output etc.,)

M

Mi-ul-vmijiIAuthor

Known Participant

I'm hoping to pull off a row at a time off the 3rd table shown in OP. I don't have any results yet as I'm not sure what code i need to get results

Michael

E

Etienne

Participating Frequently

You have to join the table t1 three times with t2, using aliases :

select t1.BuildingCode, r.Name, r.Extn, p.Name, p.Extn, m.Name, m.Extn
from t1, t2 r, t2 p, t2 m
where t1.Reception = r.ID
and t1.Porter = p.ID
and t1.Manager = m.ID

Aliases for the table t2 (r, p and m) can be what ever you want.

Etienne

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded