CODEWITHSUNDEEP
mysql
Robin Alexander
Robin Alexander

Reputation: 1004

Join two ids from one row with another table

I got two tables...

fixtures

enter image description here

and teams

enter image description here

Now I would like to display the names of both teams (lteam and vteam) playing against each other in a table. I have tried to LEFT JOIN those tables, but that does not work. Guess you know why? 

SELECT * FROM fixtures 

LEFT JOIN teams as a ON fixtures.lteam = teams.id
LEFT JOIN teams as b ON fixtures.vteam = teams.id

WHERE date_ko = '2017-05-19'

Thanks for your help!

Upvotes: 1

Views: 435

Answers (1)

Arion
Arion

Reputation: 31239

The joins are not correct. Change this:

LEFT JOIN teams as a ON fixtures.lteam = teams.id
LEFT JOIN teams as b ON fixtures.vteam = teams.id
                                            |

To this:

LEFT JOIN teams as a ON fixtures.lteam = a.id
LEFT JOIN teams as b ON fixtures.vteam = b.id
                                         |

You need to use the alias in the join too

Upvotes: 3

Related Questions