Hi,

I'm now trying to populate my 'testMatch' table (below) with data from my 'summary' table:

TESTMATCH TABLE

+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| match_id         | int(11)      | NO   | PRI | NULL    |       | 
| match_date       | date         | YES  |     | NULL    |       | 
| ground           | varchar(50)  | YES  | MUL | NULL    |       | 
| homeTeam         | varchar(100) | YES  | MUL | NULL    |       | 
| awayTeam         | varchar(100) | YES  | MUL | NULL    |       | 
| matchResult      | varchar(100) | YES  | MUL | NULL    |       | 
| manOfMatch       | varchar(30)  | YES  |     | NULL    |       | 
| homeTeam_captain | int(10)      | YES  | MUL | NULL    |       | 
| homeTeam_keeper  | int(10)      | YES  | MUL | NULL    |       | 
| awayTeam_captain | int(10)      | YES  | MUL | NULL    |       | 
| awayTeam_keeper  | int(10)      | YES  | MUL | NULL    |       | 
+------------------+--------------+------+-----+---------+-------+

There is no problem populating match_id -----> manOfMatch - it is 'homeTeam_captain', 'homeTeam_keeper', 'awayTeam_captain' and 'awayTeam_keeper' that i'm having problems bringing in.

SUMMARY TABLE

mysql> DESCRIBE SUMMARY;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| matchID         | int(11)      | NO   | PRI | NULL    |       | 
| Test            | int(11)      | YES  |     | NULL    |       | 
| matchDate       | date         | YES  |     | NULL    |       | 
| Ground          | varchar(50)  | YES  |     | NULL    |       | 
| HomeTeam        | varchar(100) | YES  |     | NULL    |       | 
| AwayTeam        | varchar(100) | YES  |     | NULL    |       | 
| matchResult     | varchar(50)  | YES  |     | NULL    |       | 
| MarginRuns      | int(11)      | YES  |     | NULL    |       | 
| MarginWickets   | int(11)      | YES  |     | NULL    |       | 
| ManOfMatch      | varchar(40)  | YES  |     | NULL    |       | 
| HomeTeamCaptain | varchar(30)  | YES  |     | NULL    |       | 
| HomeTeamKeeper  | varchar(30)  | YES  |     | NULL    |       | 
| AwayTeamCaptain | varchar(30)  | YES  |     | NULL    |       | 
| AwayTeamKeeper  | varchar(30)  | YES  |     | NULL    |       | 
+-----------------+--------------+------+-----+---------+-------+

PLAYERS TABLE

mysql> describe players;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| player_id      | int(11)      | NO   | PRI | NULL    | auto_increment | 
| player_surname | varchar(30)  | YES  |     | NULL    |                | 
| team           | varchar(100) | YES  | MUL | NULL    |                | 
+----------------+--------------+------+-----+---------+----------------+

So to clarify, eg. I want to select homeTeam_captain data FROM summary table BUT not the name, I want the corresponding player_id instead.

I assume I need to use some sort of join/subqueries to get this done... i've tried finding the correct select query first to make sure i'm pulling out the right data, and I have been using the below code for testing (thanks to user Larry_Croft for helping me with this):

select matchID, player_id, player_surname, team from players p, summary s
where
s.hometeamcaptain = p.player_surname AND s.HomeTeam = p.team ORDER BY matchID;

But this correctly brings back 65 rows (65 matches) BUT i then tried it with:

select matchID, player_id, player_surname, team from players p, summary s
where
s.hometeamKEEPER = p.player_surname AND s.HomeTeam = p.team ORDER BY matchID;

But this brings back only 61 rows (should be 65) so i must have an error in the logic.

Once I get this select to work, i then need to somehow include it into my 'INSERT INTO.....SELECT statement to put all the data into 'testMatch' table.

I hope this makes sense and thanks for your help!