You are here: Re: sql trouble « MsSQL Server « IT news, forums, messages
Re: sql trouble

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

 

Navigation:

[Reply to this message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация