Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2011
    Posts
    4

    Unanswered: multiple selects I think?

    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.

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Aug 2011
    Posts
    4
    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

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Aug 2011
    Posts
    4
    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.
    Attached Thumbnails Attached Thumbnails sqlimage.png  
    Last edited by dgsnook; 08-25-11 at 10:50. Reason: add image

  6. #6
    Join Date
    Aug 2011
    Posts
    4
    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
    Last edited by dgsnook; 08-25-11 at 12:27.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •