Need help with a query/result

[Adrian Schneider]

Ok here are my 2 tables being used:

fc_team and fc_player

I need to display the player name of each field in team [which are just ids] (ie fc_team.batsmanid1=fc_player.id)

I tried using left join but since there would be a different 'name' field for each id it won't work.

I got the query working using
[sql]
SELECT team.batsmanid1, player1.name, team.batsmanid2, player2.name
FROM fc_team team, fc_player player1, fc_player player2
WHERE team.batsmanid1=player1.id
AND team.batsmanid2=player2.id
AND team.id=$teamid
[/sql]

Upon showing this (I just used 2 of the positions for testing), I could only get one name to display, so how would I go about doing this? Can you rename the result to something like name2 instead of having 4 names, which causes conflicts?

[Marco van Herwaarden]

Are you just now designing these tables, or are they existing?

I think you are making it yourself difficult with not normalising your team table. Instead of having a batsmanid1, batsmanid2 etc in the team table, you could better make a table with the fields: teamid, playernumber, playerid. So instead of just 1 record, split it up for every batsman.

[Adrian Schneider]

edit: sorry, existing already.

Do you mean teamid, playername, playerid?

edit: or teamid, positionname, playerid

For future reference, how would I go about doing this without a different table?

[Marco van Herwaarden]

Well without modifying your table your example would just work if you chage it like:
[sql]SELECT team.batsmanid1, player1.name AS name1, team.batsmanid2, player2.name AS name2
FROM fc_team team, fc_player player1, fc_player player2
WHERE team.batsmanid1=player1.id
AND team.batsmanid2=player2.id
AND team.id=$teamid
[/sql]
The question is if you really want the tables to stay like this. How many players you can have in a team? Is this a fixed number?

[Adrian Schneider]

Oh so you can rename the result thanks a lot.

Yes I need the tables to stay like this, it's far too complicated to go back and make changes.

Thanks a lot.