Microsoft SQL Server forum

Author

sql trouble

bob@coolgroups.com

2005-07-01, 8:23 pm

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(purchasedsquar
es.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?

Hugo Kornelis

2005-07-01, 8:23 pm

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(purchasedsquar
es.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)

Erland Sommarskog

2005-07-01, 8:23 pm

(bob@coolgroups.com) writes:
> SELECT
> standardgame.gamename,
> standardgame.rowteamname,
> standardgame.colteamname,
> standardgame.dollarvalue,
> standardgame.gameid,
> standardgame.cutoffdatetime,
> standardgame.gametype,
> standardgame.gameowner,
> (100- COUNT(purchasedsquar
es.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/techin.../2000/books.asp

Sponsored Links