Hi, I'd be grateful if someone could help me and tell me what I'm doing wrong with my query. I suspect the answer is simple but I'm suffering a mental block right now.
In basic terms, I have three tables. Two are reference tables thus: 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 Then I have another table that uses the other two. It holds information about matches played at a location between two teams. 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 So, my problem is when I'm pulling the information into the web page - it's along the lines of: 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) but it doesn't return the information I want which should be something like: 1, 16/6/04 19:45, Lisbon, France, null, England, null Instead I get: 1, 16/6/04 19:45, Lisbon, France, null, France, null 1, 16/6/04 19:45, Lisbon, England, null, England, null Someone mentioned something about a crosstab query to me but the pages I've seen (including one on the mysql site) doesn't appear to help. Thanks in advance, Ken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
