CODEWITHSUNDEEP
phpmysqlsqldatabasephpmyadmin
Othman Abahossain
Othman Abahossain

Reputation: 81

Join 2 Tables ON 2 fields

I have these tables in my PHPMYADMIN, all I want is to get the name for each team using ID, I’ve been trying to use the LEFT JOIN, but it return 1 team only otherwise it will return null

This The Table match

        |   match_id    |   HomeTeam    |   AwayTeam    |
        +------------------------------------------------
        |       1       |       3       |       1       |

and this the table team 

    |   team_id     |       team_name       |
    +----------------------------------------
    |       1       |       Manchester      |
    |       2       |       Barcelona       |
    |       3       |       Real Madrid     |

and this the result I want the SQL return

    |match_id|HomeTeam|AwayTeam| Home_name   | Away_name |
    +-----------------------------------------------------
    |    1   |   3    |   1    | Real Madrid | Manchester|

I've been trying to use this SQL statement 

    SELECT `match.*`,`team_*` FROM `match`
    LEFT JOIN `team` ON `match.HomeTeam` = `team.match_id`
    LEFT JOIN `team` ON `match.AwayTeam` = `team.match_id`
    WHERE `match.match_id` = 1

any Solution ?

currently I am using this PHP code but it's not efficient I Think

    $Query  = 'SELECT * FROM match WHERE match_id = 1'
    // excute Query //

    foreach($match_array as $match)
    {
        $Query = 'SELECT FROM team WHERE team_id = '.$match['HomeTeam'].''

        $home_team_name = somthing

        $Query = 'SELECT FROM team WHERE team_id = '.$match['AwayTeam'].''
                    $away_team_name = something 
    }

Upvotes: 0

Views: 86

Answers (2)

darren102
darren102

Reputation: 2828

You could try using aliases in order to get the correct information:

SELECT `m`.`*`, `ht`.`name`, `at`.`name` FROM `match` m
LEFT JOIN `team` ht ON `m`.`HomeTeam` = `ht`.`match_id`
LEFT JOIN `team` at ON `m`.`AwayTeam` = `at`.`match_id`
WHERE `m`.`match_id` = 1

This should give you what you want

Upvotes: 0

dgw
dgw

Reputation: 13666

You'll have to left join the team table twice using table aliases.

SELECT match.*,team_home.*,team_away.* FROM match
  LEFT JOIN team team_home ON match.HomeTeam = team_home.team_id
  LEFT JOIN team team_away ON match.AwayTeam = team_away.team_id
  WHERE match.match_id = 1

Upvotes: 2

Related Questions