MySQL Select Help

Databases
1

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

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

3

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

sure

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…



        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
5

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

i would have to see your table layout

surely what you showed is not complete