Posted by Hugo Kornelis on 07/02/05 00:39

On 1 Jul 2005 12:47:02 -0700, bob@coolgroups.com wrote:

>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?

Hi Bob,

First, this is a group for MS SQL Server, not for MySQL. The query you
posted won't work in MS SQL for a completely different reason than why
it doesn't work in MySQL (the non-ANSI standard use of GROUP BY with
theoretically unpredictable results).

The logical solution in SQL Server will probably not work on MySQL,
because it contains a non-ANSI compliant construction in the ORDER BY
clause. That's why I'll give you a version that works in SQL Server AND
is within the ANSI standards - you'll have to test it to see if MySQL is
able to handle it. If it isn't, I'd advise you to take this to a MySQL
group instead of here :-)

SELECT s.gamename,
s.rowteamname,
s.colteamname,
s.dollarvalue,
s.gameid,
s.cutoffdatetime,
s.gametype,
s.gameowner,
d.squaresremaining,
CASE d.squaresremaining
WHEN 0 THEN 1
ELSE 0
END AS orderinghelper
FROM standardgame AS s
LEFT OUTER JOIN (SELECT gameid, 100-COUNT(*)
FROM purchasedsquares
GROUP BY gameid) AS d(gameid, squaresremaining)
ON s.gameid = d.gameid
WHERE s.gametype = '$gametype'
AND s.dollarvalue = '$dollarvalue'
AND s.gameowner = '$gameowner' -- Assumption;
-- this part was
-- missing in your post
ORDER BY orderinghelper ASC,
squaresremaining ASC

(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Back to original message]