MySQL Join issue
- by mouthpiec
Hi,
I have the following tables:
--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
Now I have the following SQL statment that I use to extract Team A vs Team B
SELECT sport_activity_id, T1.team_name AS TeamA, T2.team_name AS TeamB, DATE_FORMAT( DATE, '%d/%m/%Y' ) AS DATE, DATE_FORMAT( TIME, '%H:%i' ) AS TIME
FROM sportactivity
JOIN teams T1 ON home_team_fk = T1.team_id
JOIN teams T2 ON ( away_team_fk = T2.team_id
OR away_team_fk = '0' )
WHERE DATE( DATE ) >= CURDATE( )
ORDER BY DATE( DATE )
My problem is that when team B is empty, I am having irrelevant information .... it seems that it is returning all the combinations. I need a query that when team B is equal to 0, (this can occur in my scenario) I get only Team A - Team B (as 0) once.
