SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Jul 2002
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL Select Help

    I have a table that has Name, Team, Week

    I want to be able to select and have the result show me the Name and the Teams picked for that name. So data will look like this

    Mike,Tampa,8
    Mike,Buffalo,9
    Tim,New England,8
    Tim,Buffalo,9

    I was the result to look like this

    Mike,Tampa,Buffalo
    Tim,New England, Buffalo.

    Is this possible with a mysql select statement? Thanks,

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    absolutely

    except it's better to do what you want in the application layer

    for example, which of those games did mike actually pick correctly

    disclaimer: i run a database-driven nfl pool so i know how complex it can get
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jul 2002
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Could you give me an example of how to do it with SQL? It is for something quick right now. I hope to build it into the application layer eventually but need something until I can. Do you mind sharing your site and is it something you wrote yourself?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by mjstehn View Post
    Could you give me an example of how to do it with SQL?
    sure
    Code:
    SELECT name
         , GROUP_CONCAT(team) AS teams
      FROM daTable
    GROUP
        BY name
    i can't show you my site, sorry

    yes, i wrote it myself

    here's the query which retrieves each week's results for all pool players...
    Code:
            select U.userpk
                 , U.username
                 , U.firstname
                 , U.lastname
                 , N.pool
                 , G.gamedate
                 , G.gameno
                 , case when G.vscore
                           + G.hscore = 0
                         AND G.ot = 0
                        then cast(null as char(3))
                        when G.vscore
                           + G.spread
                           > G.hscore
                        then G.vteam
                        else G.hteam end  as covering_team
                 , P.pick
                 , W.winners
              FROM nfl_games as G
            inner
              join nfl_picks as P
                on P.gameno = G.gameno
            inner
              JOIN members_nfl as N
                ON N.userpk = P.userpk
            inner
              JOIN members as U
                ON U.userpk = P.userpk
               AND U.active=1
            inner
              join (
                   select userpk
                        , sum(case when nfl_games.vscore
                                      + nfl_games.hscore = 0
                                    AND nfl_games.ot = 0
                                   then 0
                                   when nfl_picks.pick
                                      = case when nfl_games.vscore
                                                + nfl_games.spread
                                                > nfl_games.hscore
                                             then nfl_games.vteam
                                             else nfl_games.hteam end
                                     then 1
                                     else 0 end) as winners
                       from nfl_games
                     inner
                       join nfl_picks
                         on nfl_picks.gameno = nfl_games.gameno
                      where nfl_games.weekno = #request.whichweek#
                     group
                         by userpk
                   ) as W
                on W.userpk = P.userpk
             WHERE G.weekno = #request.whichweek#
            ORDER
                BY N.pool
                 , CASE WHEN U.userpk = #request.userpk#
                        THEN 0 ELSE 1 END
                 , W.winners desc
                 , U.lastname
                 , U.firstname
                 , G.gamedate
                 , G.gameno
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Jul 2002
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks. I was just curios about the site if you open it up for entry to people? I am always looking at different pools to join? I know you have to be careful. I keep mine pretty locked down and make sure the people that are entering are friends of people that have been in, so I understand. Do you sell the code as shareware or anything or is the code opensource?

    Is there a way that the Group_Concat(teams) can be setup to be in separate fields in the query. i.e.

    Name Team1 Team2
    Mike Buffalo New England

    Thanks for all your help.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by mjstehn View Post
    Is there a way that the Group_Concat(teams) can be setup to be in separate fields in the query.
    i would have to see your table layout

    surely what you showed is not complete
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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