sql trouble

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

[Hugo Kornelis]

Re: sql trouble

On 1 Jul 2005 12:47:02 -0700, bob@coolgroups. com wrote:
[color=blue]
>Could someone help me get the following SQL statement working?
>
>SELECT
>standardgame.g amename,
>standardgame.r owteamname,
>standardgame.c olteamname,
>standardgame.d ollarvalue,
>standardgame.g ameid,
>standardgame.c utoffdatetime,
>standardgame.g ametype,
>standardgame.g ameowner,
>(100-COUNT(purchased squares.gameid) ) AS squaresremainin g
>FROM standardgame
>LEFT OUTER JOIN
>purchasedsquar es 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,
>squaresremaini ng ASC
>
>
>The problem is... MySQL doesn't seem to want to let me use
>squaresremaini ng 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]

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.cutoffdatetim e,
s.gametype,
s.gameowner,
d.squaresremain ing,
CASE d.squaresremain ing
WHEN 0 THEN 1
ELSE 0
END AS orderinghelper
FROM standardgame AS s
LEFT OUTER JOIN (SELECT gameid, 100-COUNT(*)
FROM purchasedsquare s
GROUP BY gameid) AS d(gameid, squaresremainin g)
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,
squaresremainin g ASC

(untested)

Best, Hugo
--

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

[Erland Sommarskog]

Re: sql trouble

(bob@coolgroups .com) writes:[color=blue]
> 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]

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.cutoffdateti me, s1.gametype, s1.gameowner,
s2.squaresremai ning
FROM standardgame s1
JOIN (SELECT s.gameid, (100 - COUNT(p.gameid) ) AS squaresremainin g
FROM standardgame s
LEFT JOIN purchasedsquare s p ON s.gameid = p.gameid
WHERE gametype='$game type'
AND dollarvalue = '$dollarvalue'
AND gameowner = '
GROUP BY s.gameid) AS s2
ORDER BY CASE s2.squaresremai ning WHEN 0 THEN 1 ELSE 0 END ASC,
s2.squaresremai ning 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@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp