Query Question

[mooger35]

I'm new to php and have been picking most things up pretty quick but I've hit a brick wall.

I'm building a website for a hockey league I play in and I need some help with a query to the MYSQL db.

The "schedule table" contains 'homeid' and 'visitorid'. The plan is to have them pull the teamname from the "team table" (which contains 'teamid' and 'teamname').

How do I pull the teamname from the teamtable for both homeid and visitorid. I can do one or the other, just not both.

Hope that was clear... many thanks to anyone who can help.

[StupidScript]

Welcome aboard, mooger35!

Would you please post an example of the two table structures and the query you are using?

You would either want (1) a common field to key off of (ties the two tables together) or (2) run two queries where you get the first bit of data (homeid) from the first table then use that query's results to get the corresponding data (teamid) from the second table.

(1)

SELECT team.teamid,team.teamname

FROM team

LEFT JOIN schedule

ON team.teamid=schedule.homeid

(2)

$gethome=mysql_query("SELECT homeid FROM schedule");

while ($row=mysql_fetch_array($gethome)) {

$thisid=$row['homeid'];

$getteam=mysql_query("SELECT teamid,teamname FROM team WHERE teamid='$thisid'");

etc.

[mooger35]

I can do it with two queries. Figured that out last night but it can get sloppy (at least the way I was doing it).

As for the table structure.

TEAMS
teamid - tinyint(2) - auto_increment
teamname - varchar(15

SCHEDULE (relevant fields)
homeid - tinyint(2)
visitorid - tinyint(2) UNSIGNED ZEROFILL No 00

[homeid visitor]id match up with teamid

[coopster]

You can join the table to itself.

SELECT 
 home.teamname AS home, 
 visitor.teamname AS visitor 
 FROM schedule 
 INNER JOIN teams AS home ON (schedule.homeid = home.teamid) 
 INNER JOIN teams AS visitor ON (schedule.visitorid = visitor.teamid) 
 WHERE schedule.gamedate = NOW() 
;

I pooched the WHERE clause, you probably already have something meaningful.

[mooger35]

I needed to add a GROUP BY variable but it works now.

Thanks!