sql

[bob]

Could someone help me get the following SQL statement working?

SELECT
standardgame.gamename,
standardgame.rowteamname,
standardgame.colteamname,
standardgame.dollarvalue,
standardgame.gameid,
standardgame.cutoffdatetime,
standardgame.gametype,
standardgame.gameowner,
(100-COUNT(purchasedsquares.gameid)) AS squaresremaining
FROM standardgame
LEFT OUTER JOIN
purchasedsquares ON standardgame.gameid = purchasedsquares.gameid
where gametype='$gametype' and dollarvalue = '$dollarvalue' and
gameowner = '
GROUP BY standardgame.gameid
order by
CASE squaresremaining WHEN 0 THEN 1 ELSE 0 END ASC,
squaresremaining ASC
The problem is... MySQL doesn't seem to want to let me use
squaresremaining in that case statement since it's not an official
column name. Any idea how I can reference squaresremaining in the case
statement?

[Larry Linson]

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

<bo*@coolgroups.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

Could someone help me get the following SQL statement working?

SELECT
standardgame.gamename,
standardgame.rowteamname,
standardgame.colteamname,
standardgame.dollarvalue,
standardgame.gameid,
standardgame.cutoffdatetime,
standardgame.gametype,
standardgame.gameowner,
(100-COUNT(purchasedsquares.gameid)) AS squaresremaining
FROM standardgame
LEFT OUTER JOIN
purchasedsquares ON standardgame.gameid = purchasedsquares.gameid
where gametype='$gametype' and dollarvalue = '$dollarvalue' and
gameowner = '
GROUP BY standardgame.gameid
order by
CASE squaresremaining WHEN 0 THEN 1 ELSE 0 END ASC,
squaresremaining ASC
The problem is... MySQL doesn't seem to want to let me use
squaresremaining in that case statement since it's not an official
column name. Any idea how I can reference squaresremaining in the case
statement?

[dkintheuk]

Shouldn't that line read...

CASE (100-COUNT(purchasedsquares.ga*meid)) WHEN 0 THEN 1 ELSE 0 END
ASC,
(100-COUNT(purchasedsquares.ga*meid)) 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.