#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2011
    Posts
    11
    Rep 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.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2011
    Posts
    11
    Rep 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!
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    excellent, and congrats for fixing that other error as well

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo