MySql multiple select from two tables and join their results to a third table -

i have 3 tables: players, playersarchive, races. players , playersarchive tables same structure.

in players , playersarchive have playerid , name.

in races have in 1 row:

raceid, record1playerid, record2playerid, record3playerid, record4playerid , record5playerid

the task select whole races table, instead of player id fields should return names.

e.g.:

races table: raceid: 1 record1playerid: 2 record2playerid: 1 record3playerid: 0 record4playerid: 0 record5playerid: 0 

---

players table: playerid: 1 name: jhon 

---

playersarchive table: playerid: 2 name: jack 

---

result: 1     jack     jhon     null     null     null 

you have 2 problems data structure. first should have separate table players in each race, 1 row per player/race combination. putting them in separate columns on single row bad idea.

the second problem putting names in 2 different tables. question, cannot tell if both players table , playersarchive table necessary. so, i'll assume archive not necessary.

the solution join races table table players information:

select r.*,        p1.name name1,        p2.name name2,        p3.name name3,        p4.name name4,        p5.name name5 races r left outer join      players p1      on r.record1playerid = p1.playerid left outer join      players p2      on r.record1playerid = p2.playerid left outer join      players p3      on r.record1playerid = p3.playerid left outer join      players p4      on r.record1playerid = p4.playerid left outer join      players p5      on r.record1playerid = p5.playerid left outer join 

if data in both players , playersarchive, players might need be:

     (select distinct playerid, name ((select p.* players) union (select pa.* playersarchive)) t) p1 . . . 

this combines 2 tables looking name.