Table Stucture & generating query

[Naez]

Okay so I've got a couple tables:

 

 

articles
-----------
id - primary
cat_id - INT index foreign #links up with categories table
author_id - INT index foreign # links up with user table 
title - varchar(255)
body - longtext
url - varchar(255)  # [b]url-safe-article-title-for-seo-links[/b]
-----------

categories
-----------
id - primary
category - varchar(255)
-----------

 

 

The only thing is, I want to seperate things off by level too.

 

So for instance:

 

"Advanced" articles will carry say category id's 3,4,5

"Intermediate" will carry 2

"Novice" will carry cat_id 1

 

with room for expansion of course.

 

 

Now I know a couple ways to do this... add a new table, or add a parent_id field in categories.  But I'm not sure the best way to do this so hralp!

 

And more importantly, how to generate a big-ass query that will do all the following:

 

[b]Novice:[/b]
- Novice Category 1 (2 articles)
[b]Intermediate[/b]
- Intermediate Category 1 (5 articles)
- Intermediate Category 2 (2 articles)
- Intermediate Category 3 (20 articles)
[b]Advanced:[/b]
- Advanced Category 1 (2 articles)

# I know I'll need some semi-intricate joins and groupings, and a COUNT()

 

I'm not exactly the SQL JOINs master so hopefully I can get some hralp.

 

Also on side note should I use longtext or something else for the body?

 

Thanks!

[Barand]

If it's only going to be "level" and "category" it's easier to have a "level" table.

 

If you are going to have varying sub-cats and sub-sub-cats then go for the parentid in category

 

Assuming the former

 

SELECT l.level, c.category, COUNT(*) as itemcount

FROM articles a

JOIN categories c ON a.cat_id = c.id

JOIN levels l ON c.level_id = l.id

GROUP BY l.id, c.category