articles from many subcategories

[mihail]

Hi guys,

 

here is my newest problem:

 

I have 3 tables

 

- categories

cat_id

parent

name

 

- articles

art_id

title

 

- articles_to_categories

id

cat_id

art_id

 

 

with this structure I have for example:

 

category 1 (cat_id =1, parent=0)

- subcategory 1.1 (cat_id =2, parent=1)

- subcategory 1.2 (cat_id =3, parent=1)

 

and article with art_id 1 in both subcategories, so in articles_to_categories I have 2 records

 

- id=1, cat_id=2, art_id=1

- id=1, cat_id=3, art_id=1

 

Now I need to pull out all distinct articles from all subcategories of parent category. For example, I need to pull out all articles from category 1 wich means all distinct articles from subcategory 1.1 and subcategory 1.2.

 

Somebody please help mi with that. Thanks in advance!

[Oldiesmann]

This is kind of confusing...

 

You have one article in two separate sub-categories, but you only want to pull each article once, regardless of how many sub-categories it's in?

[mihail]

To quote myself:

 

"Now I need to pull out all distinct articles from all subcategories of parent category"

 

So 1 article can be in one or more subcategorias of a parent cat., but it should be displayed only once.

 

One more exampe:

 

- if I click Category 1, I need to get all distinct article from Category 1, Subcategory 1.1 and subcategory 1.2.

- if I click Subcatgory 1.1 I need to get only articles fron that category

 

Also any subcategory can have subcategories etc, so I think this should be a recursive function.