CODEWITHSUNDEEP
mysql
user2363993
user2363993

Reputation: 125

Joining Three Tables in MySQL database

I want to join three tables. First table (result) has got result of games and contains date, team a, team b and final score. Second table (line up) has detail of line up i.e list of players which played for a team on the given date i.e. date, team, player1, player2, player3 etc. Third table (players) contain information about each player i.e name, height, weight date of birth etc. I want to take out information from all three tables using join. I want final value to be date, team, player1, player1_height, player1_weight, player2, player2_height, player2_weight,and so on. The structure of three tables is as under please :

I am joining first two tables to get date, team, player1,player2, etc using following query . 

  SELECT 
         result.Date, 
         result.Team, 
         result.Team_Score, 
         lineup.player1, 
         lineup.player2, 
         lineup.player3, 
         lineup.player4, 
         lineup.player5     
  FROM   
         result 
  JOIN
         lineup 
  ON     
         result.date = lineup.date;

What can I do to join this result with third table to get height and weight of each player in one row? Thanks

Upvotes: 0

Views: 106

Answers (1)

Randy
Randy

Reputation: 16677

if i understand, just keep joining the same table for each player

select  result.Date, result.Team, result.Team_Score
      , lineup.player1, p1.weight
      , lineup.player2, p2.weight
      , lineup.player3, p3.weight
      , lineup.player4, p4.weight
      , lineup.player5, p5.weight
from result, players p1, players p2, players p3, players p4, players p5, lineup 
where result.date = lineup.date
and p1.playerid = lineup.playe1
and p2.playerid = lineup.playe2
and p3.playerid = lineup.playe3
and p4.playerid = lineup.playe4
and p5.playerid = lineup.playe5;

Upvotes: 0

Related Questions