php and mysql issues...

[mkosmosports]

This question may be more of a mysql problem than php, but I have to give it a go here (you folks seem to be knowledgable in both areas..:), since the answer may have a large impact on me. Ive created a table which has two columns indexing the same column in another table (Please let me know if this a nono immediately).

Ie. I have two tables 'teams' and team_players'. The 'teams' table contains a unique ID and the name of the team. The 'team_players' table then contains two columns with that unique team ID.

Im then doing a mysql query with php and I want to retrieve two team names, based on the two columns in the 'team_players' table that contain two different team ID's. However, there is only one column that contains those team names......

I am completely stumped as to how to do this...maybe because its impossible and I need to restructure my db..(Im crossing my fingers that its not though)....

Any help would be much appreciated.....

Thanks.

[trq]

Something like....

[code]
SELECT team.name, team_players.name GROUP BY team.name;
[/code]