SQL Design

Is this possible

[ukgimp]

I have a category table and I am able to pull out all the children of and level of the category. I have another table that contains a related category derived from the main table. Now separately it is possible to get the children and count the numbers of records that are party of that category. Is it possible to combine the two or do I just need to run two separate queries.

Get Children
SELECT id, etc etc from categories where cat_id = ‘’

Count Number of record that have a cat ID
SELECT COUNT(*), category FROM pages GROUP BY category

And then bugger around combining the two or can it be done in one query?

Cheers

[daisho]

How many levels? 1, 2, or dynamic?

[ukgimp]

it is dynamic. I suppose I could do a count once and use the array repeatidly but the seems heavy and resources. I was hoping for a quick query on each page for its categori id.

Does that make sense? :)

[ukgimp]

a bit of work and the solution comes to you.

I have changed some of the names to make it easier to read.

SELECT COUNT(*), categories.name, categories.id, pages.category, categories.parent FROM categories, pages WHERE categories.id = pages.category AND pages.accepted = 'y' AND categories.parent = ‘$currentcat’ GROUP BY categories.id

This seems to work :)