How to make this query?

Question

I have two tables:

Teams

• id
• name

Games

• id
• date
• idlocal
• idvisitor

idlocal and idvisitor are the id of the teams in the teams table.

I need to make a SQL query that returns the date, the NAME of the first team, and the NAME of the second team, but I don't know how to do it.

Thanks

Answer 1

SELECT date,t1.name,t2.name FROM 
games g 
INNER JOIN teams t1 on t1.id=g.idlocal
INNER JOIN teams t2 on t2.id=g.idvisitor