Query gives double results

[heraldic2]

Greetings,

 

You folks have helped me many times in the past and I have come, once again, seeking the wisdom of the elders.

 

I have a fantasy football league.  I have unsuccessfully created a query that will return the 5 all time lowest winning scores (all time being after 2008 due to a scoring system over haul).

 

Query as it is now:

select setup.game_id, home.teamname as Home_Team, max(home_score.score) as Home_Score, away.teamname as Away_Name, 
max(away_score.score) as Away_Score, ABS(Min(home_score.score) - min(away_score.score)) as Diff, week, year
from owners as home
join game_scores as home_score on home_score.team_id = home.owner_id
join game_setup as setup on setup.game_id = home_score.game_id
join game_scores as away_score on away_score.game_id = setup.game_id AND away_score.team_id != home_score.team_id
join owners as away on away.owner_id = away_score.team_id
where year > 2008
and home_score.score < 300
and away_score.score < 300
group by home.teamname, away.teamname
order by setup.game_id
limit 10

 

Results are:

game_id Home_Team Home_Score Away_Name Away_Score Diff week year

276 Team McCarthy 299.4 Vanilla Gorillaz 287.4 12.0 Week 1 2009

276 Vanilla Gorillaz 287.4 Team McCarthy 299.4 12.0 Week 1 2009

331 Indian Trail Sillynannies 243.1 Carolina Mighty Mackerel 189.2 53.9 Week 8 2009

331 Carolina Mighty Mackerel 189.2 Indian Trail Sillynannies 243.1 53.9 Week 8 2009

403 Pink Panthers 291.3 Speed Demons 272.0 19.3 Round 3 2009

403 Speed Demons 272.0 Pink Panthers 291.3 19.3 Round 3 2009

406 Team Super Trojans 225.7 Indian Trail Sillynannies 276.0 50.3 Round 3 2009

406 Indian Trail Sillynannies 276.0 Team Super Trojans 225.7 50.3 Round 3 2009

596 Squad Sweepers 217.4 America Enforcers 287.8 70.4 Week 7 2011

596 America Enforcers 287.8 Squad Sweepers 217.4 70.4 Week 7 2011

 

 

As you can see the query does return the correct games, however it lists the games twice and flips the home team and away team. 

 

Any clue as to what I did incorrectly?

 

As always thank you very much for your time.

 

 

[requinix]

You didn't do anything wrong. The thing with joining a table against itself is that you'll come across the same row twice: once on the left side of the JOIN, once on the right side. It's like multiplying: you can get to 15 by multiplying 3*5, but 5*3 does it too.

 

Put in a condition that compares the two teams in such a way that only one combination is valid. For instance, only where the left team's ID is less than the right team's ID. Unless there's something in particular you'd like to see (eg, alphabetical, or the home team must be on the left, or the loser on the right) the actual condition itself doesn't matter.

[heraldic2]

Thank you very much for the advice.  I didn't even think that joining a table onto itself would cause that.  I will see what I can do to get the condition working.

 

Thanks again.