MySQL Join issue

Question

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.

Answer 1

Use a LEFT OUTER JOIN instead of an INNER JOIN. That is, change this:

JOIN teams T2 ON ( away_team_fk = T2.team_id OR away_team_fk =  '0' ) 

to this:

LEFT JOIN teams T2 ON away_team_fk = T2.team_id

This should return nulls for the columns in T2 in the case that away_team_fk = 0 (assuming that no such row exists).

Answer 2

Have you tried using the DISTINCT keyword?

SELECT DISTINCT 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 ) 

This will make sure that if a row is repeated, all data the same, it is only shown once.

Like if you had

1 abc abc 10/22/10 12:00:00
1 abc abc 10/22/10 12:00:00
2 abc dcb 10/22/10 12:00:00

You would have

1 abc abc 10/22/10 12:00:00
2 abc dcb 10/22/10 12:00:00

returned instead