MySQL UNION problem - can't get correct data out

Answer 1

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.

Answer 2

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.