Posted by Erland Sommarskog on 07/02/05 00:58

(bob@coolgroups.com) writes:
> 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?

The best way is to wrap the query into a derived table. There is however
a second problem: you cannot group only by gameid. All non-aggregate columns
must be in the GROUP BY clause. A much cleaner solution is to keep just
the core to the derived table, and then join with standardgame again.
This gives us:

SELECT s1.gamename, s1.rowteamname, s1.colteamname, s1.dollarvalue,
s1.gameid, s1.cutoffdatetime, s1.gametype, s1.gameowner,
s2.squaresremaining
FROM standardgame s1
JOIN (SELECT s.gameid, (100 - COUNT(p.gameid)) AS squaresremaining
FROM standardgame s
LEFT JOIN purchasedsquares p ON s.gameid = p.gameid
WHERE gametype='$gametype'
AND dollarvalue = '$dollarvalue'
AND gameowner = '
GROUP BY s.gameid) AS s2
ORDER BY CASE s2.squaresremaining WHEN 0 THEN 1 ELSE 0 END ASC,
s2.squaresremaining ASC


(I've also introduced aliases, to make the query less verbose and
more readable.) Note that there is a syntax error just before GROUP
BY, a unclosed string literal.

The above syntax is fine in MS SQL Server. If you are really using
MySQL, I can't tell whether this syntax is good. In theory it should
be, because it's all ANSI compatible. Hm, wait, the CASE expression
in the ORDER BY may not be; you might have to add a column to the
result set with the expression for that.

In any case, this newsgroup is for MS SQL Server, so if you are using
MySQL, you are likely to get better help elsewhere.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]