Getting Two items from Same table in SELECT statment

Question

Hi,

I an SQL SELECT statement I need to extract the name of two teams, taking both teams from the same table. Eg Below

SELECT sport_activity_id, (team A), (team B), date, time 
FROM sportactivity, teams 
WHERE competition_id_fk = 2

For (team A) and (team B) I have an team_id, which is a FK for the table 'teams'

Is it possible to get the following result from these tables by SQL? 1, Barcelona, Arsenal, 01/01/2000, 20:00

the two table are the following:

table sportactivity

sport_activity_id, home_team_fk, away_team_fk, competition_id_fk, date, time

(tuple example) -> 1, 33, 41, 5, 2010-04-14, 05:40:00

table teams

team_id, team_name

(tuple example) -> 1, Algeria

Answer 1

Yes, you just need to join with teams twice:

SELECT sport_activity_id, T1.team_name, T2.team_name, date, time 
FROM sportactivity
JOIN teams T1 ON home_team_fk = T1.team_id
JOIN teams T2 ON away_team_fk = T2.team_id
WHERE competition_id_fk = 2

Answer 2

SELECT sport_activity_id, teamA.team_Name, teamB.team_Name, date, time 
FROM sportactivity
INNER JOIN teams teamA ON sportactivity.home_team_fk = teamA.team_ID
INNER JOIN teams teamB ON sportactivity.away_team_fk = teamB.team_ID
WHERE competition_id_fk = 2