Stuck on an SQL Query

Dev Shed Forums Sponsor:

December 3rd, 2011, 06:34 AM

YoungL

Registered User

Join Date: May 2011

Posts: 11

Time spent in forums: 2 h 15 m 42 sec
Reputation Power: 0

Stuck on an SQL Query

Code:

SELECT ibf_arcade_cats.cat_name, (

SELECT count( * )
FROM ibf_arcade_games
WHERE ibf_arcade_cats.c_id = ibf_arcade_games.gcat
) AS cat_total
FROM ibf_arcade_cats

The above query works, it returns the total number of games which have the category ID in the cats table for each Cat ID.

Now I want to compare "cat_total" with the already set total in the table ibf_arcade_cats... this is what I have tried:

Code:

SELECT ibf_arcade_cats.cat_name, (

SELECT count( * )
FROM ibf_arcade_games
WHERE ibf_arcade_cats.c_id = ibf_arcade_games.gcat
) AS cat_total
FROM ibf_arcade_cats WHERE  num_of_games <> cat_total

but it tells me that cat_total is an unknown column... is this query possible to do, I just want to get a report of those rows where the total is not currently up to date.

Any help greatly appreciated.

December 3rd, 2011, 07:35 AM

r937

SQL Consultant

Dev Shed God 43rd Plane (26000 - 26499 posts)

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,439

Time spent in forums: 3 Months 1 Week 2 Days 13 h 57 m 19 sec
Reputation Power: 4141

cat_total is a column alias, and you cannot use a column alias in the WHERE clause of the same query

however, what you can do is push your entire query (marked in red below) down one level into a subquery, and then you can use the alias name in the outer query

Code:

SELECT t.*
  FROM ( SELECT ibf_arcade_cats.cat_name
              , ( SELECT COUNT(*)
                    FROM ibf_arcade_games
                   WHERE ibf_arcade_games.gcat = ibf_arcade_cats.c_id
                ) AS cat_total
           FROM ibf_arcade_cats  ) AS t
 WHERE t.num_of_games <> t.cat_total

when you try this, you will discover yet another problem

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

December 3rd, 2011, 10:04 AM

YoungL

Registered User

Join Date: May 2011

Posts: 11

Time spent in forums: 2 h 15 m 42 sec
Reputation Power: 0

Excellent,

Got it working, that had been bugging me for a good hour!

Final query was:

Code:

SELECT t . *
FROM (

	SELECT ibf_arcade_cats.cat_name, ibf_arcade_cats.num_of_games, (

		SELECT COUNT( * )
		FROM ibf_arcade_games
		WHERE ibf_arcade_games.gcat = ibf_arcade_cats.c_id
	) AS cat_total
	FROM ibf_arcade_cats
) AS t
WHERE t.cat_total <> t.num_of_games

And it worked a treat!

December 3rd, 2011, 12:31 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,439

Time spent in forums: 3 Months 1 Week 2 Days 13 h 57 m 19 sec
Reputation Power: 4141

excellent, and congrats for fixing that other error as well

Stuck on an SQL Query

Developer Shed Advertisers and Affiliates