join - Joining tables in sql for many to many relation -

create table matches (id int, team_a_id int, team_b_id int, winner_id int); <br> create table teams (id, name ) 

here 2 tables , want join them in way team_a_id, team_b_id , winner_id changed name of team , entire table remains same first table. unable think how can achieve it. please can me out.

o/p <br> id | first_team_name | second_team_name | winner_name | 

a simple join can that. you'll have join teams once per name want, if match guaranteed have winner, can use regular join instead of left join below show games if there's no winner yet.

select m.id,         t1.name first_team_name,         t2.name second_team_name,         t3.name winner_name matches m join teams t1 on t1.id = team_a_id join teams t2 on t2.id = team_b_id left join teams t3 on t3.id = winner_id 

an sqlfiddle test with.