Hi,
Having trouble getting the results I need with this query.
Basically have two tables, content and contentheaders.
The contentheaders table lists the headings for various menu items, such as White Wine, Red Wine, Champagne etc.
The content table contains the actual menu items that fall under the various headings, such as chardonnay, pinot blanc, riesling under the White Wine heading, and so on.

I am trying to use a select query to display all the headings for the wine webpage, with a count of the number of menu items in the content table for each heading.

The query I have is:

SELECT contentheaders.headername, contentheaders.headerID,
COUNT(content.contentID) As total From contentheaders, content Where contentheaders.contentarea = '$strID' And contentheaders.headerID = content.header Group By contentheaders.headername, contentheaders.headerID

At the moment the query doesn't display any results at all, since there aren't any menu items for those headings yet, but I would still like the query to display the heading names, with a count of zero next to each one, and of course with an accurate count once the content table is populated.

This is the end result I am looking for:

Header Names Number of items
White wine 0
Red Wine 0
Champagne 0


Instead it is returning nothing.

Any help would be greatly appreciated.
Thanks,

Paul

When you say it's returing nothing, do you mean nothing or an error?

Am I mistaken or are you asking 'two tables' for a record set?

If so you need a join, unless the answer to the above question is no - I would do something like: (note primaryKeyName and forignKeyName are to replaced with relavent names)

SELECT COUNT(content.contentID) As total, contentheaders.headername,
contentheaders.headerID From contentheaders
INNER JOIN content ON contentheaders.primaryKeyName = content.forignKeyName
Where contentheaders.contentarea = '$strID'
And contentheaders.headerID = content.header
Group By contentheaders.headername, contentheaders.headerID

If you want to dispaly 0 instead of a NULL value you can
do until recordSetName.EoF
   if recordSetName(somenumber) == NULL then
      response.write 0
   else
      response.write recordSetName(somenumber)
   end if
   recordSetName.movenext
loop


Wind is your friend
Matt

Thanks for the help - when I say it returns nothing, there are zero records returned. Asking two tables for a recordset.

Tried the query you wrote but it just resulted in the same, zero records returned. I need an outer join, somthing that returns all the records from the contentheaders table (although just the headers that match the content area specified in the querystring) and then a count of the corresponding records in the content table, zero if there aren't any.

Thanks again.