SELECT
C.pkCategories as thiscat,
C.category_name,
C.fkParentCategory,
(SELECT COUNT(I.pkCategories)
FROM Categories I
WHERE I.fkParentCategory = C.pkCategories) AS subcatnum
FROM
Categories C
WHERE
C.fkParentCategories = 0
You can't use reference names inside a query how you have them. They are
only used in the resultset.
----- Original Message -----
From: "Kay Smoljak" <[EMAIL PROTECTED]>
To: "SQL" <[EMAIL PROTECTED]>
Sent: Thursday, November 29, 2001 12:24 AM
Subject: Sub-select problem
> Hi all,
>
> Another tricky one - I guess could do this in two separate queries, but
> goddamn it I don't want to!!
>
> Here's my query:
>
> SELECT Categories.pkCategories as thiscat,
> Categories.category_name,
> Categories.fkParentCategory,
> (
> SELECT COUNT(pkCategories)
> FROM Categories
> WHERE fkParentCategory = thiscat
> ) AS subcatnum
> FROM Categories
> WHERE Categories.fkParentCategories = 0
>
>
> As you can probably see, I'm trying to get the number of subcategories
> in each parent category, but I don't know how to make the subselect
> reference the primary key from the outer select.
>
> Ouch... I'm not even making sense to myself.
>
> The error I'm getting is "Invalid column name 'thiscat'." Which makes
> perfect sense, but I don't know how to fix it.
>
> Any help greatly appreciated!
>
> K.
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
