Mutli joins to a single table. 1

[randy4126]

I have a table that has two different ID's that link to another table. I want to be able in a single query pull back all the information I need. My we host is running a fair old version of MySQL so I can't do sub quieries. Here is what I came up with and everytime it runs it throws away one of the team columns.

SELECT S.ScheduleTime, H.Team , S.HomeGoals, V.Team, S.VisitingGoals, L.Location, S.Result
FROM schedule S, teams V, Location L, teams H
Where S.VisitingTeamID = V.TeamID
AND S.HomeTeamID = H.TeamID
AND S.LocationID = L.LocationID


Any help you could give would be greatly appricated. Thanks.
Randy Coooper

Randy

 

[r937]

use column aliases

SELECT S.ScheduleTime, H.Team as HomeTeam, S.HomeGoals, V.Team as VisitingTeam, S.VisitingGoals, L.Location, S.Result FROM ...



r937.com | rudy.ca

 

[randy4126]

Thanks for your reply. I have found that my query does work but it is the php doing something very woerd and droping columns. I gues that is a question for another forumn. Thanks

Randy

 

[randy4126]

After thinking about what you were saying I added that to my query and it worked. You are a genious. Thanks. This was my PHP statement

$line = mysql_fetch_array($result, MYSQL_ASSOC

And now I see it was getting confused because the coulmn was being named the same. Wow that would have taken me a long time to figure it out. Thanks again for your help.

Randy

 

[towerbase]

Randy,

Why not give r937 a star? All you need do is click on the Thank r937 for this valuable post link. It's a great way of saying Thank You

Andrew
Hampshire, UK