I have a page that currently makes 3 calls to the Db. I was wondering if there is a way to compact them into 1 join call. If so is it more efficient to make 3 simple calls to the Db or 1 advanced call. Please take a look at my example below. Any help would be greatly appreciated. I have left out a few fields in each table but have included the neccessary fields.
Table 1: schedule
id - INT
timestamp - TIMESTAMP
hteam - INT
vteam - INT
field - INT
Table 2: teams
id - INT
name - CHAR
Table 3: fields
id - INT
name - char
ok. The schedule table stores id numbers for each column that corresponds to the ids in the teams and fields tables. So an example might look like:
SELECT id, timestamp, hteam, vteam, field FROM schedule;
id | timestamp | hteam | vteam | field
--------------------------------------
2 |20010418000000| 3 | 15 | 2
Right now I am querying all three tables and then displaying using arrays and loops with PHP. What I would like is to have a query that could do a join and return the proper names in the result like below so I don't have to call the Db 3 times every time the page is called. hteam and vteam need to find the proper row from teams and display the name field. The problem I have had is that when I try to do a join it shows up like this:
id | timestamp | hteam | vteam | field
--------------------------------------
2 |20010418000000| Mets | Mets | Shea
instead of:
id | timestamp | hteam | vteam | field
--------------------------------------
2 |20010418000000| Mets | Expos | Shea
Any suggestions?
Thanks again
Table 1: schedule
id - INT
timestamp - TIMESTAMP
hteam - INT
vteam - INT
field - INT
Table 2: teams
id - INT
name - CHAR
Table 3: fields
id - INT
name - char
ok. The schedule table stores id numbers for each column that corresponds to the ids in the teams and fields tables. So an example might look like:
SELECT id, timestamp, hteam, vteam, field FROM schedule;
id | timestamp | hteam | vteam | field
--------------------------------------
2 |20010418000000| 3 | 15 | 2
Right now I am querying all three tables and then displaying using arrays and loops with PHP. What I would like is to have a query that could do a join and return the proper names in the result like below so I don't have to call the Db 3 times every time the page is called. hteam and vteam need to find the proper row from teams and display the name field. The problem I have had is that when I try to do a join it shows up like this:
id | timestamp | hteam | vteam | field
--------------------------------------
2 |20010418000000| Mets | Mets | Shea
instead of:
id | timestamp | hteam | vteam | field
--------------------------------------
2 |20010418000000| Mets | Expos | Shea
Any suggestions?
Thanks again