PLEASE Help With My Fantasy League SQL....

Hi Everyone,

Firstly, best to mention that i am a relative novice when it comes to SQL.... there is sooooooo much info out there that it is just becoming more and more confusing.

My first question must seem like a no-brainer.... but if i am running an access database with ASP based SQL scripting does that automatically mean i am using MS SQL? Does My SQL only work with PHP??

My next query may be a CHALLENGE that might inspire a response:

I have been busy beevering away trying to build a fantasy soccer league for my work mates. It uses ASP scripting which encompasses SQL statements that relates to an access database. 2 tables 'Teamdata' and 'Players'.

'Team data' includes the manager name, team name, and the name of each player from 1 to 11 in seperate columns

'Players' includes all of the possible players (approx 200)names, cost, and points accumulated.

After a lot of time and effort i managed to write an SQL script that returned the points for each team based on the players they have selected. and then list them in order based upon points received for the overall player selection of each team.

My Script:

SELECT DISTINCT(SELECT SUM(playerpoints) FROM players WHERE Teamdata.Player1 = players.player OR Teamdata.Player2 = players.player OR Teamdata.Player3 = players.player OR Teamdata.Player4 = players.player OR Teamdata.Player5 = players.player OR Teamdata.Player6 = players.player OR Teamdata.Player7 = players.player OR Teamdata.Player8 = players.player OR Teamdata.Player9 = players.player OR Teamdata.Player10 = players.player OR Teamdata.Player11 = players.player) AS Points, Team, manager FROM Teamdata, players


Problem 1: The teams are listed in ascending order, i.e. the team with the most points appears at the bottom.... ideally it needs to be at the top of the table. Although i have attempted using the ORDER BY 'points' DESC command it seems to have no effect because it conflicts with DISTINCT? I used the DISTINCT function because without it i had lots of teams being repeated..... can anyone solve this problem

Problem 2: Although my script works its takes absoloooooooooooooooootely ages!! What am i doing wrong? how can i make the process of selecting the data quicker??

If anyone can spare the time to help it would be greatly appreciated

I look forward to any responses.....

Cheers,

Dan