advances query help (works with 1 fault)

[Destramic]

hey guys im creating a league and below you can see the tables and the query its self that im using.

 

now the query resturns the results i need but the one thing it doesnt return is any teams that are in the league that dont have any match results...im not sure if the way i have designed this whole thing is correct...but im after pointers and i want to know how i can return the other teams to show up in the league that dont have any points or any data within the league_match and league_match_results.

 

thank you

 

tables -

 

leagues

league_id

game_id

league_name

win_points

loss_points

draw_points

 

games

game_id

game_platform_id

game_name

game_abbreviation

 

teams

team_id

team_name

 

league_matches

league_match_id

league_id

challenger_id

opponent_id

 

league_match_results

league_match_result_id

league_match_id

team_id

result

 

 

working query with 1 fault.

SET @rank := 0;
SET @points := 0;
SET @drawing := 0;

SELECT *,
        @rank := IF(@points = points, IF(@points = '0', @rank + 1, @rank), @rank + 1), 
                @points := points,
                @drawing := IF(@points = points, IF(@points = '0', @drawing = '0', @drawing = '1'), @drawing = '0'),
                  IF(@drawing = '1', @rank + 1, @rank) as rank,  @drawing := '0'
                  FROM(
                  SELECT t.team_id,
                         t.team_name, 
                         l.league_name,
                         COUNT(r.league_match_result_id) AS 'matches_played',
                         SUM(IF(r.result='Win', 1, 0)) AS `wins`,
                         SUM(IF(r.result='Loss', 1, 0)) AS `losses`,
                         SUM(IF(r.result='Draw', 1, 0)) AS `draws`,
                         SUM(IF(r.result='Win', 3, IF(r.result='Draw', 1, IF(r.result='Loss', 0, 0)))) AS `points`
                  FROM teams t
                  LEFT JOIN league_match_results r ON r.team_id = t.team_id
                  LEFT JOIN team_leagues tl ON tl.team_id = t.team_id
	  LEFT JOIN league_matches m ON r.league_match_id = m.league_match_id
                  LEFT JOIN leagues l ON l.league_id = m.league_id
                  LEFT JOIN games g ON g.game_id = l.game_id
                  WHERE l.league_name = 'Counter-Strike Europeon Team Death Match' 
                  AND g.game_abbreviation = 'CSS'
                  GROUP BY t.team_id
                  ORDER BY points DESC, t.team_name) AS x

[fenway]

That's because you included a field in your where clause that from a table that is LEFT JOIN'ed.

 

Move "g.game_abbreviation = 'CSS'" into the on clause for the games table and you're set.

[Destramic]

thank you..it works great i also took the left join of league matches out as it wasnt really need at all for the data i needed to be returned

 

SET @rank := 0;
SET @points := 0;
SET @drawing := 0;

SELECT *,
                     @rank := IF(@points = points, IF(@points = '0', @rank + 1, @rank), @rank + 1), 
                         @points := points,
                         @drawing := IF(@points = points, IF(@points = '0', @drawing = '0', @drawing = '1'), @drawing = '0'),
                         IF(@drawing = '1', @rank + 1, @rank) as rank,
                         @drawing := '0'
                  FROM(
                  SELECT t.team_id,
                         t.team_name, 
                         COUNT(r.league_match_result_id) AS 'matches_played',
                         SUM(IF(r.result='Win', 1, 0)) AS `wins`,
                         SUM(IF(r.result='Loss', 1, 0)) AS `losses`,
                         SUM(IF(r.result='Draw', 1, 0)) AS `draws`,
                         SUM(IF(r.result='Win', 3, IF(r.result='Draw', 1, IF(r.result='Loss', 0, 0)))) AS `points`
                  FROM teams t
                  LEFT JOIN league_match_results r ON r.team_id = t.team_id
                  LEFT JOIN team_leagues tl ON tl.team_id = t.team_id
                  LEFT JOIN leagues l ON l.league_name = 'Counter-Strike Europeon Team Death Match'
                  LEFT JOIN games g ON g.game_abbreviation = 'CSS'
                  GROUP BY t.team_id
                  ORDER BY points DESC, t.team_name) AS x

 

just one more thing is the way i've designed the tables and is the query a good way of doing this?

 

thank you again

[fenway]

Looks good to me.