How to get data from the database from the table, if 1 (column with primary key) and the same column are referenced by 2 other columns (2 foreign keys and 1 primary?

Question

SQL

In general, the essence of the task is as follows: there is a table where the names of the teams are written, and their number is timid (has a primary key).
There is also a table where there are 2 columns the home team and the away team, these columns contain the same numbers as in the teamid column, you need to make a connection from the home team and the away team to the teamid and so that after the id of the X team
and the G teams , you could find out their name, the question is: is it possible to do this and how to do it, if possible? I tried to just make foreign keys to the column, but then when I create a query in the editor, it does not display anything, empty lines.

Answer 1

SELECT hv.homeId, h.Name, hv.visitorId, v.Name
FROM home_visitor hv, teamInfo h, teamInfo v
WHERE hv.homeId = h.Id AND hv.visitorId = v.Id