hello all

I'm not sure if I have gone down the wrong path here but this is what I want to do. I have a table with the following fields.

id,hp1id,hp1name,hp2id,hp2name,hp3id,hp3name,hp1s, hp2s,hp3s,ap1id,ap1name,ap2id,ap2name,ap3id,ap3nam e,ap1s,ap2s,ap3s,hpb,apb,htotal,atotal,fixtureid,s eason,leagueid

The table stores data from a scorecard from snooker games. A scorecard looks like this:
Scorecard | Cynghrair Snwcer Ceredigion Snooker League

Now then, I want to be able to display alist of all the players with their total scores and games played etc. Problem is a player can be hp1id or hp2id or hp3id or ap1id or ap2id or ap3id (hp1 means home player 1 and so on.).

I can successfully retrieve one players resutls (i.e playerid 47) and do the necessary artihmetic, based on a player id like this:

select COALESCE(sum(hp1s),0) + (select COALESCE(sum(hp2s),0)from scorecard where hp2id = 47) + (select COALESCE(sum(hp3s),0)from scorecard where hp3id = 47)+ (select COALESCE(sum(ap1s),0)from scorecard where ap1id = 47)+ (select COALESCE(sum(ap2s),0)from scorecard where ap2id = 47) + (select COALESCE(sum(ap3s),0)from scorecard where ap3id = 47)as totalscore , (select count(*) * 3 as totalgames from scorecard where hp1id = 47 or hp2id = 47 or hp3id = 47 or ap1id = 47 or ap2id = 47 or ap3id = 47
)from scorecard where hp1id = 47

But how do I get the results for all the other players like player 48, 49 50 etc? I need to order the final results too by games won, games lost, games played etc.

I have another table with playerids in if that helps.

Thanks, hope this makes sense.

I know a bit about snooker but really don't understand what each of the fields in the table mean. Can you provide more information as to what is being stored in the table? For example, hp1id is this home player 1 id?

yes no problem. this is how it works. When a snooker match takes place a team (TeamA) plays against a team (TeamB). both teams have 3 players each. Each player plays 3 games against his opposite number i.e TeamA player 1 plays 3 games against TeamB player 1, then TeamA player 2 plays 3 games against TeamB player 2 and finally TeamA player 3 plays 3 games against TeamB player 3. A scorecard is then created based on the results as can be seen on our live site here Scorecard | Cynghrair Snwcer Ceredigion Snooker League.

Scorecard database fields:

id = unique id of the record
hp1id = id of the first home team player
hp1name = name of the first home team player
hp1s = score of the first home team player, number of games won
htotal = total number of won games by home team
fixtureid = id of the fixture which corresponds to the fixtures table
season = the season this fixture was played i.e 2011
leagueid = id of the league, we have two leagues.

the other fields mean the same thing for players 2 and 3 and then ap1id is for the away team etc.

I want to extract from this scorecard table player name, total games played, total won, total lost, average. A semi hard coded version can be seen here Player Profiles Div1 | Cynghrair Snwcer Ceredigion Snooker League

My sql above will successfully retreive the information I want for an individual player, in that case it's for playerid 47. The question is how can I get the results for all players in leagueid = 4 and season = 2011 so I can produce a sortable table like the one here Player Profiles Div1 | Cynghrair Snwcer Ceredigion Snooker League

I have looked into this and there does not seem to be an easy way of getting all results out. However, if you modify your data model this may help. For example, when one team meets another team the player number is irrelevant, what is important is to keep track of the opponent and the score.

So if you change this to have

Teams
teamid
team name

Matches
matchid
matchdate
hometeamid
awayteamid

Results
matchid
playerid
teamid
opponentid
frameswon

Each entry going in to Results should have an corresponding entry for the opponentid i.e. playerid = 1 and opponent id = 2 with frameswon 3 should also have playerid = 2, opponent id = 1, frameswon 0.

I think this layout might help you get the results of your query more efficiently.

I think I understand. What I've got now is the attached image and what I want to output is a list of names and their total scores and total games. Each record in there means they have played 3 games so I have to times their total entries by 3.

output:

Adrian Williams played 6 won 4
Aled Harries played 6 won 3
Andrew James played 3 won 1
Dafydd Phillips played 6 won 4

and so on.

actually I think this might be it, would you say this is ok.

SELECT distinct playerid as le_player, (select sum(score) from playerresults where playerid = le_player), (select count(playerid) as totalgames from playerresults where playerid = le_player) from playerresults