MySQL UNION problem - can't get correct data out

I need to normalise a table which contains cricket players:

 
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| matchID    | int(11)      | NO   | PRI | 0       |       | 
| innings    | int(11)      | NO   | PRI | 0       |       | 
| Bat1Name   | varchar(30)  | YES  |     | NULL    |       | 
| Bat1Score  | int(11)      | YES  |     | NULL    |       | 
| balls1     | int(11)      | YES  |     | NULL    |       | 
| sixes1     | int(11)      | YES  |     | NULL    |       | 
| fours1     | int(11)      | YES  |     | NULL    |       | 
| out1       | varchar(10)  | YES  |     | NULL    |       | 
| catcher1   | varchar(30)  | YES  |     | NULL    |       | 
| bowler1    | varchar(30)  | YES  |     | NULL    |       | 
| Bat2Name   | varchar(30)  | YES  |     | NULL    |       | 
| Bat2Score  | int(11)      | YES  |     | NULL    |       | 
| balls2     | int(11)      | YES  |     | NULL    |       | 
| sixes2     | int(11)      | YES  |     | NULL    |       | 
| fours2     | int(11)      | YES  |     | NULL    |       | 
| out2       | varchar(10)  | YES  |     | NULL    |       | 
| catcher2   | varchar(30)  | YES  |     | NULL    |       | 
| bowler2    | varchar(30)  | YES  |     | NULL    |       | 
| Bat3Name   | varchar(30)  | YES  |     | NULL    |       | 
| Bat3Score  | int(11)      | YES  |     | NULL    |       | 
| balls3     | int(11)      | YES  |     | NULL    |       | 
| sixes3     | int(11)      | YES  |     | NULL    |       | 
| fours3     | int(11)      | YES  |     | NULL    |       | 
| out3       | varchar(10)  | YES  |     | NULL    |       | 
| catcher3   | varchar(30)  | YES  |     | NULL    |       | 
| bowler3    | varchar(30)  | YES  |     | NULL    |       | 
| Bat4Name   | varchar(30)  | YES  |     | NULL    |       | 
| Bat4Score  | int(11)      | YES  |     | NULL    |       | 
| balls4     | int(11)      | YES  |     | NULL    |       | 
| sixes4     | int(11)      | YES  |     | NULL    |       | 
| fours4     | int(11)      | YES  |     | NULL    |       | 
| out4       | varchar(10)  | YES  |     | NULL    |       | 
| catcher4   | varchar(30)  | YES  |     | NULL    |       | 
| bowler4    | varchar(30)  | YES  |     | NULL    |       | 
| Bat5Name   | varchar(30)  | YES  |     | NULL    |       | 
| Bat5Score  | int(11)      | YES  |     | NULL    |       | 
| balls5     | int(11)      | YES  |     | NULL    |       | 
| sixes5     | int(11)      | YES  |     | NULL    |       | 
| fours5     | int(11)      | YES  |     | NULL    |       | 
| out5       | varchar(10)  | YES  |     | NULL    |       | 
| catcher5   | varchar(30)  | YES  |     | NULL    |       | 
| bowler5    | varchar(30)  | YES  |     | NULL    |       | 
| Bat6Name   | varchar(30)  | YES  |     | NULL    |       | 
| Bat6Score  | int(11)      | YES  |     | NULL    |       | 
| balls6     | int(11)      | YES  |     | NULL    |       | 
| sixes6     | int(11)      | YES  |     | NULL    |       | 
| fours6     | int(11)      | YES  |     | NULL    |       | 
| out6       | varchar(10)  | YES  |     | NULL    |       | 
| catcher6   | varchar(30)  | YES  |     | NULL    |       | 
| bowler6    | varchar(30)  | YES  |     | NULL    |       | 
| Bat7Name   | varchar(30)  | YES  |     | NULL    |       | 
| Bat7Score  | int(11)      | YES  |     | NULL    |       | 
| balls7     | int(11)      | YES  |     | NULL    |       | 
| sixes7     | int(11)      | YES  |     | NULL    |       | 
| fours7     | int(11)      | YES  |     | NULL    |       | 
| out7       | varchar(10)  | YES  |     | NULL    |       | 
| catcher7   | varchar(30)  | YES  |     | NULL    |       | 
| bowler7    | varchar(30)  | YES  |     | NULL    |       | 
| Bat8Name   | varchar(30)  | YES  |     | NULL    |       | 
| Bat8Score  | int(11)      | YES  |     | NULL    |       | 
| balls8     | int(11)      | YES  |     | NULL    |       | 
| sixes8     | int(11)      | YES  |     | NULL    |       | 
| fours8     | int(11)      | YES  |     | NULL    |       | 
| out8       | varchar(10)  | YES  |     | NULL    |       | 
| catcher8   | varchar(30)  | YES  |     | NULL    |       | 
| bowler8    | varchar(30)  | YES  |     | NULL    |       | 
| Bat9Name   | varchar(30)  | YES  |     | NULL    |       | 
| Bat9Score  | int(11)      | YES  |     | NULL    |       | 
| balls9     | int(11)      | YES  |     | NULL    |       | 
| sixes9     | int(11)      | YES  |     | NULL    |       | 
| fours9     | int(11)      | YES  |     | NULL    |       | 
| out9       | varchar(10)  | YES  |     | NULL    |       | 
| catcher9   | varchar(30)  | YES  |     | NULL    |       | 
| bowler9    | varchar(30)  | YES  |     | NULL    |       | 
| Bat10Name  | varchar(30)  | YES  |     | NULL    |       | 
| Bat10Score | int(11)      | YES  |     | NULL    |       | 
| balls10    | int(11)      | YES  |     | NULL    |       | 
| sixes10    | int(11)      | YES  |     | NULL    |       | 
| fours10    | int(11)      | YES  |     | NULL    |       | 
| out10      | varchar(10)  | YES  |     | NULL    |       | 
| catcher10  | varchar(30)  | YES  |     | NULL    |       | 
| bowler10   | varchar(30)  | YES  |     | NULL    |       | 
| Bat11Name  | varchar(30)  | YES  |     | NULL    |       | 
| Bat11Score | int(11)      | YES  |     | NULL    |       | 
| balls11    | int(11)      | YES  |     | NULL    |       | 
| sixes11    | int(11)      | YES  |     | NULL    |       | 
| fours11    | int(11)      | YES  |     | NULL    |       | 
| out11      | varchar(10)  | YES  |     | NULL    |       | 
| catcher11  | varchar(30)  | YES  |     | NULL    |       | 
| bowler11   | varchar(30)  | YES  |     | NULL    |       | 
| extras     | int(11)      | YES  |     | NULL    |       | 
| wides      | int(11)      | YES  |     | NULL    |       | 
| noBalls    | int(11)      | YES  |     | NULL    | 开发者_高级运维      | 
| byes       | int(11)      | YES  |     | NULL    |       | 
| legByes    | int(11)      | YES  |     | NULL    |       | 
| score      | int(11)      | YES  |     | NULL    |       | 
| wickets    | int(11)      | YES  |     | NULL    |       | 
| overs      | float        | YES  |     | NULL    |       | 
| runrate    | float        | YES  |     | NULL    |       | 
| team       | varchar(100) | YES  |     | NULL    |       | 
+------------+--------------+------+-----+---------+-------+

The rows I am interested in is all Bat?Name and Team fields eg. Bat1Name, Bat2Name....Bat11Name and the team they belong to.

I have created a player 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    |                | 
    +----------------+--------------+------+-----+---------+----------------+
3 rows in set (0.03 sec)

I successfully selected the player's names (without duplicates) into the player's table using the below UNION code:

INSERT INTO players (player_surname)
SELECT DISTINCT bat1Name from details
UNION
SELECT DISTINCT bat2Namen from details
UNION
SELECT DISTINCT bat3Name from details
(etc............................)
SELECT DISTINCT bat11Name from details; 

This gave me 300 records which is correct.

But then realised I needed to include the teams so I added team:

 
INSERT INTO players (player_surname, team)
SELECT DISTINCT bat1Name,team from details
UNION
SELECT DISTINCT bat2Namen,team from details
UNION
SELECT DISTINCT bat3Name,team from details
(etc.............................)
SELECT DISTINCT bat11Name,team from details;

But this provides me with 310 records and says (for example) that Smith plays for UK, New Zealand and south africa but this is incorrect. It has also incorrectly pulled out a couple of others. I'm at a loss!

I assume my method of UNION is flawed so how can I get all batsmen out of the table with their corresponding team?

If there are two players with the same surnames that were playing in the different teams they will merge in the first query but not the second:

bat1   bat2   team
Smith  Jones  UK
Doe    Smith  Zealand

The first query will return Smith once, the second one twice.

It is most probable that your first query is wrong, not the second one. Try running this for your first query:

SELECT  batname
FROM    (
        SELECT  bat1Name AS batname, team
        FROM   details
        UNION
        SELECT  bat2Name, team
        FROM   details
        UNION  …
        ) q

You don't need to do DISTINCT here: UNION will take care of this automatically.

As Quassnoi says it is probably the first query that is wrong as the second one will pull out the right data.

Also the player table should only store the player related data with a relation to another table storing the teams.