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

Tim,New England,8

I was the result to look like this

Tim,New England, Buffalo.

Is this possible with a mysql select statement? Thanks,


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

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?


     , GROUP_CONCAT(team) AS teams
  FROM daTable
    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
          FROM nfl_games as G
          join nfl_picks as P
            on P.gameno = G.gameno
          JOIN members_nfl as N
            ON N.userpk = P.userpk
          JOIN members as U
            ON U.userpk = P.userpk
          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
                   join nfl_picks
                     on nfl_picks.gameno = nfl_games.gameno
                  where nfl_games.weekno = #request.whichweek#
                     by userpk
               ) as W
            on W.userpk = P.userpk
         WHERE G.weekno = #request.whichweek#
            BY N.pool
             , CASE WHEN U.userpk = #request.userpk#
                    THEN 0 ELSE 1 END
             , desc
             , U.lastname
             , U.firstname
             , G.gamedate
             , G.gameno

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.

i would have to see your table layout

surely what you showed is not complete