> table: locations > fields: location_id (primary key), location_name > example data: 1, Lisbon; 2, Porto > > table: teams > fields: team_id (primary key), team_name > example data: 1, France; 2, England > > table: matches > fields: match_id, match_datetime, match_location (foreign key to locations table), team1_id (foreign key to teams table), team1_score, team2_id (foreign key to teams table), team2_score example data: 1, 2004/06/16 19:45:00, 1, 1, null, 2, null > > SELECT m.match_id, m.match_datetime, m.match_location, m.team1_id, m.team1_score, m.team2_id, m.team2_score, l.location_name, t.team_name as team1_name, t.team_name as team2_name > FROM matches m, teams t, locations l > WHERE l.location_id = m.match_location AND (t.team_id = m.team1_id OR t.team_id = m.team2_id) > You'll want the teams table joined twice with the matches table: once to retrieve the name of team1_id and a second time for the name of team2_id. The OR in the WHERE clause is actually the problem which causes the multiple rows in the output...
SELECT m.match_id, m.match_datetime, m.match_location, m.team1_id, m.team1_score, m.team2_id, m.team2_score, l.location_name, t.team_name as team1_name, tt.team_name as team2_name FROM matches AS m JOIN teams AS t ON m.team1_id = t.team_id JOIN teams AS tt ON m.team2_id = tt.team_id JOIN locations AS l ON l.location_id = m.match_location (I always put the JOIN condition near the actual JOIN, just to make sure that I still understand it later) Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
