Counting Items in Categories

Ive got this monster which will give me a parent categoryName and the number of records linked to a child of that category, I want to use it for a directory where the list of categories has the number of records in brackets next to them. Note: a A listing will show up in each category count it is associated with

Like

Accommodation (10)
Real Estate(30)
Automotive(2)
Education(1)...

Select trade_category.iCategory_Name,Listing_category.iPa rentID,count(Listing_category.iCategoryID) as num
from Listing_category,trade_category Where Listing_category.iParentID = trade_category.iCategoryID Group by
Listing_category.iParentID,trade_category.iCategor y_Name
Union ALL
Select Freecategory.sName,Listing_category.iParentID,coun t(Listing_category.iCategoryID) as num
from Listing_category,Freecategory Where Listing_category.iParentID = Freecategory.iFreeID Group by
Listing_category.iParentID,Freecategory.sName

Which Produces

Real Estate 12401 12
Extreme Sports 3 4

I would Like to get the same query to produce a list of all the empty records too.
so
ID Count
Accommodation 6112 0
Real Estate 12401 12
retail 12402 0
Extreme Sports 3 4
Cycling 5 0There is no such concept of an 'empty record'. If you were to describe to me an 'empty record', what would it be? In situations similar to what you describe, a record can have one of the following characteristics:

- Contain null values for one or more of its fields
- Not be returned with respect to some given criteria.

But there is no mention of an 'empty record' in relational theory or in any Database implementation. Looking at your query, I can't think of what it is you're actually trying to achieve, except in the case where a parent category may have no children, you need to return a result set similar to the following:

{ParentID, ChildCount}
ParentA, 0

When viewed in this way, the problem becomes a trivial LEFT JOIN query that will return all rows from set A irrespective of the contents of set B. In your example however, instead of returning the rows from Set B you will just return a count of the rows.

Select
SetA.columnA,
count(SetB.columnA)
from
SetA

left outer join SetB
on SetA.ColumnA = SetB.ColumnB

Remember: Simplification should be the goal of every developer. A paraphrased quote I once heard said: Perfection is reached not when you can no longer add to it, but when you can no longer take anything away.|||Ive found a short term solution will look at speeding it up when I have some spare time, and I have the live version working so it makes a bit more sense.

Start of December