One foreign key for two columns

I have two tables, Results and Teams

Teams has the following columns:

team_id
team_name

Results has the following columns:

match_id
home_team (foreign key references teams [team_id])
away_team (foreign key references teams [team_id])
score
yellow_cards
red_cards

When I write query:

select team_name, score 
FROM teams, results 
WHERE results.home_team = teams.team_id
  AND results.away_team = teams.team_id

it would give me empty set.

Is it issue with the fact that I use same foreign key for two different columns in the same table?

The query results are correct, as your query finds all teams and results where a team played against itself (home_team = away_team).

You probably want to use 2 joins to teams in you query:

SELECT 
    home.team_name AS home_team_name, 
    away.team_name AS away_team_name, 
    r.score 
FROM 
    results AS r
  JOIN teams AS home 
    ON r.home_team = home.team_id
  JOIN teams AS away 
    ON r.away_team = away.team_id ;

StackExchange Database Administrators Q#83794, answer score: 10