CODEWITHSUNDEEP
phpmysqlsqljoin
Samuel Bolduc
Samuel Bolduc

Reputation: 19193

How to use two joins on the same column

I have two tables : one table it matchs and the other is teams. In matches, I have a date, the time and two IDs (the two teams). In teams I have an ID and the name of the team. What I want to do is get the list of matchs with the names of the teams. I know the INNER JOIN can do this but I'm not sure how ... here's what I have :

SELECT teams.name AS team1, teams.name AS team2, matchs.id, matchs.date, matchs.time
FROM matchs
INNER JOIN teams ON teams.id=matchs.team1

Obviously, all I get is the name of the team1. How can I have both team names?

Thank you

Upvotes: 2

Views: 132

Answers (4)

Gohn67
Gohn67

Reputation: 10648

SELECT t1.name AS team1, t2.name AS team2, matchs.id, matchs.date, matchs.time
FROM matchs
INNER JOIN teams t1 ON t1.id = matchs.team1 
INNER JOIN teams t2 on t2.id = matchs.team2

Upvotes: 0

Justin Pihony
Justin Pihony

Reputation: 67135

You want to do something like below. Just use aliases to differentiate the two joins and make your code more expressive.

SELECT teams1.name AS team1, teams2.name AS team2, matchs.id, matchs.date, matchs.time
FROM matchs
    INNER JOIN teams AS teams1
        ON teams1.id=matchs.team1
    INNER JOIN teams AS teams2 
        ON teTeams2ams.id=matchs.team2

Upvotes: 2

Code Magician
Code Magician

Reputation: 24032

The easiest way to do this is to perform two joins:

SELECT t1.name AS team1, t2.name AS team2, 
       matchs.id, matchs.date, matchs.time
FROM matchs
    INNER JOIN teams t1 
        ON t1.id = matchs.team1  
    INNER JOIN teams t2 
        ON t2.id = matchs.team2

The tables are aliased (t1, t2) to differentiate between each dataset in the query.

Upvotes: 1

alexn
alexn

Reputation: 59002

You can achieve this by doing another join and giving the table aliases:

SELECT t1.name AS team1, t2.name AS team2, matchs.id, matchs.date, matchs.time
FROM matchs
INNER JOIN teams as t1 ON t1.id = matchs.team1
INNER JOIN teams ON as t2 t2.id = matchs.team1

Upvotes: 0

Related Questions