SnookerSA Home
Tournaments
Masters Billiards Tournaments
select concat(n.Firstname,' ',n.Lastname) as 'Name' , sum(x.P) as 'P' , sum(x.W) as 'W' , sum(x.D) as 'D' , sum(x.L) as 'L' , sum(x.For) as 'For' , sum(x.Against) as 'Against' , sum(x.Diff) as 'Diff' , sum(x.PremPoints) as 'PremPoints' , y.Matches , y.Opponents from ( -- League matches select pr.idName , count(*) as 'P' , sum(pr.Won) as 'W' , sum(pr.Draw) as 'D' , sum(pr.Lost) as 'L' , sum(pr.Points) as 'For' , sum(pr2.Points) as 'Against' , sum(pr.Points)-sum(pr2.Points) as 'Diff' , sum(pr.Won)-sum(pr.Lost) as 'PremPoints' from teamresult tr, schedule s ,playerresult pr ,comp c ,teamresult tr2 /* Players Opponent */ ,playerresult pr2 ,team t, team t2 where c.idComp= and s.idComp=c.idComp and s.RoundType='M' and pr.idName>0 and s.idSchedule=tr.idSchedule and tr.idSchedule=tr2.idSchedule and tr.idTeamResult=pr.idTeamResult and tr2.idTeamResult=pr2.idTeamResult and pr.position=pr2.position and tr.idTeam!=tr2.idTeam and t.idTeam=tr.idTeam and t2.idTeam=tr2.idTeam group by pr.idName UNION ALL -- Masters matches player1 select p.idName , count(*) as 'P' , sum(IF(Result1=1,1,0)) as 'W' , sum(IF(Result1=0,1,0)) as 'D' , sum(IF(Result1=-1,1,0)) as 'L' , sum(Score1) as 'For' , sum(Score2) as 'Against' , sum(Score1)-sum(Score2) as 'Diff' , sum(Result1) as 'PremPoints' from masters_match m, masters_player p where m.idMasters=4 and m.idMasters_Player1=p.idMasters_Player group by p.idName UNION ALL -- Masters matches player2 select p.idName , count(*) as 'P' , sum(IF(Result2=1,1,0)) as 'W' , sum(IF(Result2=0,1,0)) as 'D' , sum(IF(Result2=-1,1,0)) as 'L' , sum(Score2) as 'For' , sum(Score1) as 'Against' , sum(Score2)-sum(Score1) as 'Diff' , sum(Result2) as 'PremPoints' from masters_match m, masters_player p where m.idMasters=4 and m.idMasters_Player2=p.idMasters_Player group by p.idName) x left join ( -- count of Masters Matches and Opponents select p.idName , count(opponent) as 'Matches' , count(distinct opponent) as 'Opponents' from (select idMasters_player1 as 'idMasters_player', idMasters_player2 as 'opponent' from masters_match where idMasters=4 UNION ALL select idMasters_player2 as 'idMasters_player', idMasters_player1 as 'opponent' from masters_match where idMasters=4 ) x ,masters_player p where x.idMasters_player=p.idMasters_player group by x.idMasters_player) y on y.idName=x.idName ,name n where n.idName=x.idName group by x.idName order by PremPoints desc, Diff desc
Get ladder. Errno=1064. You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'and s.idComp=c.idComp and s.RoundType='M' and pr.idName>0 ' at line 32