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
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
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...
, case when G.vscore
+ G.hscore = 0
AND G.ot = 0
then cast(null as char(3))
else G.hteam end as covering_team
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
, sum(case when nfl_games.vscore
+ nfl_games.hscore = 0
AND nfl_games.ot = 0
= case when nfl_games.vscore
else nfl_games.hteam end
else 0 end) as winners
on nfl_picks.gameno = nfl_games.gameno
where nfl_games.weekno = #request.whichweek#
) as W
on W.userpk = P.userpk
WHERE G.weekno = #request.whichweek#
, CASE WHEN U.userpk = #request.userpk#
THEN 0 ELSE 1 END
, W.winners desc
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