sql

[bob@coolgroups.com]

Could someone help me get the following SQL statement working?

SELECT
standardgame.ga mename,
standardgame.ro wteamname,
standardgame.co lteamname,
standardgame.do llarvalue,
standardgame.ga meid,
standardgame.cu toffdatetime,
standardgame.ga metype,
standardgame.ga meowner,
(100-COUNT(purchased squares.gameid) ) AS squaresremainin g
FROM standardgame
LEFT OUTER JOIN
purchasedsquare s ON standardgame.ga meid = purchasedsquare s.gameid
where gametype='$game type' and dollarvalue = '$dollarvalue' and
gameowner = '
GROUP BY standardgame.ga meid
order by
CASE squaresremainin g WHEN 0 THEN 1 ELSE 0 END ASC,
squaresremainin g ASC


The problem is... MySQL doesn't seem to want to let me use
squaresremainin g in that case statement since it's not an official
column name. Any idea how I can reference squaresremainin g in the case
statement?

[Larry Linson]

Re: sql

Microsoft Access database software comes with the Jet database engine, which
is installed by default. It also comes with the free MSDE, desktop version
of Microsoft SQL Server, which must be installed manually. It can be used
with any ODBC-compliant server database, which includes MySQL, but the
Access newsgroup is highly unlikely to be the best, or even a good place, to
get detailed answers about MySQL's flavor of SQL. I'd suggest you find a
newsgroup devoted to MySQL and improve your chances of getting the answer
you need.

Larry Linson
Microsoft Access MVP

<bob@coolgroups .com> wrote in message
news:1120247321 .948395.186310@ g44g2000cwa.goo glegroups.com.. .[color=blue]
> Could someone help me get the following SQL statement working?
>
> SELECT
> standardgame.ga mename,
> standardgame.ro wteamname,
> standardgame.co lteamname,
> standardgame.do llarvalue,
> standardgame.ga meid,
> standardgame.cu toffdatetime,
> standardgame.ga metype,
> standardgame.ga meowner,
> (100-COUNT(purchased squares.gameid) ) AS squaresremainin g
> FROM standardgame
> LEFT OUTER JOIN
> purchasedsquare s ON standardgame.ga meid = purchasedsquare s.gameid
> where gametype='$game type' and dollarvalue = '$dollarvalue' and
> gameowner = '
> GROUP BY standardgame.ga meid
> order by
> CASE squaresremainin g WHEN 0 THEN 1 ELSE 0 END ASC,
> squaresremainin g ASC
>
>
> The problem is... MySQL doesn't seem to want to let me use
> squaresremainin g in that case statement since it's not an official
> column name. Any idea how I can reference squaresremainin g in the case
> statement?
>[/color]

[dkintheuk]

Re: sql

Shouldn't that line read...

CASE (100-COUNT(purchased squares.gameid )) WHEN 0 THEN 1 ELSE 0 END
ASC,
(100-COUNT(purchased squares.gameid )) ASC;

I think you have to specify the calculation as a field to get the SQL
parsed properly.

Regards,

Rob.

Let us know if that works... but next time, like Larry says, head to an
SQL group.