MYSQL: How to find player_id from surname?

views:

answers:

+2 Q:

MYSQL: How to find player_id from surname?

+1 A:

Are there overlapping names? If so, also assure that the teams correspond (add s.HomeTeam = p.team to the where block). If there are players with the same name in one team, you will have to solve these conflicts manually.

To select all the keepers/captains at once, you need left outer joins. I guess it will be one join per player, so you have to join the same table 4 times.

Once you've selected the right data, you can insert it in your testMatch table with INSERT ... SELECT.

Larry_Croft 2010-02-27 18:51:43

great, thanks very much

Theresa 2010-02-27 20:12:18

OK, my query works, but I still don't know how to join/subquery in order to put this query result into my 'matchTest' table?ThanksTheresa

Theresa 2010-02-27 21:37:24

Hi,I'm afraid the s.HomeTeam = p.team worked for hometeam_captain but not for hometeam_keeper because it returns 61 results and it should be 65. Sorry for my dense-ness, i'm new to this and have an assignment due on Wed!

Theresa 2010-02-28 11:05:19

Well, then I guess these keepers aren't in your database...or maybe their names are written slightly different in both tables?! You can select all games from summary and left join the players. Then you see which games have a *NULL* player and see why there is no match.

Larry_Croft 2010-02-28 13:42:05

Hi Larry,I've managed to get the query i think i need:insert into testMatch (match_id, homeTeam_captain)select matchID, hometeamcaptain=(select players.player_id from players, `summary` where players.player_surname = summary.HomeTeamCaptain and players.team = summary.HomeTeam) from summary;BUT the error says 'RROR 1242 (21000): Subquery returns more than 1 row' - I know it returns 65 rows... and i want the 65 rows to be inserted into my table so why won't it let me? Thanks again

Theresa 2010-02-28 13:44:14

'insert into testMatch (match_id, homeTeam_captain)select matchID, (select players.player_id from players, summary where players.player_surname = summary.HomeTeamCaptain and players.team = summary.HomeTeam) AS hometeamcaptainfrom summary;'

Theresa 2010-02-28 13:45:23

ansaurus

tags:

views:

answers:

MYSQL: How to find player_id from surname?

related questions