Puoi utilizzare la query seguente:

select casa.Nome_Tean,ospiti.Nome_Team,score_A,score_B from risultati join teams as casa on Team_A=casa.id join teams as ospiti on Team_B=ospiti.id;

Antonio