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.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Get the mailserver that powers this list at http://www.coolfusion.com
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
