multiple joins between tables

[Mi-ul-vmijiI]

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

[Daverms]

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.,)

[Mi-ul-vmijiI]

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

[Etienne]

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

[Mi-ul-vmijiI]

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'

[Mi-ul-vmijiI]

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__-]

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