ansaurus

Question

mysql problem: populate table from another table but referencing ID instead of name

Answer 1

A:

Well for me it looks that there is eitehr a hometeamKEEPER that has a null value or that the value of hometeamKEEPER is not in the players table.

Using the following query you should be able to find the hometownKEEPER that are not in the players table:

SELECT matchID, player_id, player_surname, team 
FROM players p 
     RIGHT JOIN summary s ON p.hometeamKEEPER = p.player_surname AND 
                             s.HomeTeam = p.team
ORDER BY matchID;

Obalix 2010-02-28 11:48:34

Hi, thanks for your response but homeTeamkeeper definitely doesn't have any NULL values and all homeTeam/awayTeam_captain/keepers are in the player table.

Theresa 2010-02-28 12:14:57

Hi,<code>select player_idFROM players p right JOIN `summary` s ON s.awayteamcaptain = p.player_surname and s.awayteam = p.team;</code> works but when i try and use this query to insert into my 'testMatch' table I get the following error:<code>ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'</code>I used to have homeTeam_captain as a foreign key to the player_id primary key... but in testMatch the same name (homeTeam_captain) can appear more than once (not unique) so I have removed the FK contraint but still get this error.Thanks

Theresa 2010-02-28 12:21:04

INSERT INTO testMatch (homeTeam_captain)select player_idFROM players p right OUTER JOIN `summary` s ON s.hoemteamcaptain = p.player_surname and s.hometeam = p.team;

Theresa 2010-02-28 12:22:38

ignore typing error above - should be hometeamcaptain

Theresa 2010-02-28 12:23:10

If you are using outer joins, you have to remember that unmatched entries are included in thre resultset, i.e. if you have a row in table a that has no key in table b then the outer join will still list both table in the result set, providing null for the values in the select that it cannot match. Therefore, I suggest that you look at your data using left and right joins to determine what goes wrong. For your insert query you should use an inner join to esure data consistency.

Obalix 2010-02-28 13:00:28

Hi, the above 'select' query brings me the correct data. But as soon as I add it as a 'subquery' to my 'insert into testMatch' code, it brings back the error 'ERROR 1242 (21000): Subquery returns more than 1 row

Theresa 2010-03-01 11:04:57

This just meas that there are two entries on one side of the join for one on the right side, or even that there is a m:n in you resultset. You could use distict to throw out the duplicates.

Obalix 2010-03-01 13:32:12

ansaurus

tags:

views:

answers:

mysql problem: populate table from another table but referencing ID instead of name

related questions