Query help

[tibberous]

I have a table called categories, it holds categories and subcategories, but they only go one level deep (a subcategory can not be a parent)

 

ID

Name

Parent

1

A main category

0

2

Another main category

0

3

A subcategory

1

4

A subcategory

2

 

Now, I have another table with items. Those items have a category id.

 

I am trying to search those items by main and subcategories. Search by subcategories is easy, but to search by main category requires searching all of its subcategories.

 

Is there a way I can do this with subqueries or joins, or some other neat bit of mySQL trickery? I used to know subqueries, and somehow forgot them and just started using more lines of PHP and more simple queries.

 

Does anyone know how to do this?

 

Thanks,

 

Trent

[GingerRobot]

Ok, assuming the following table structure:

 

Table: categories

 

id|          name           |pid

--------------------------

1|First Category          | 0

2|Second Category      | 0

3|First Sub Category    | 1

4|Second Sub Category| 1

 

Table: items

 

id|name|cid

-------------

0|item 1|1

1|item 2|3

2|item 3|2

3|item 4|4

 

The following query should get all of the item where their cid is 1, OR where there cid is a sub category of category 1:

SELECT `id`,`item`,`cid` FROM `items` WHERE `cid`= 1 OR `cid` IN (SELECT `id` FROM `categories` WHERE `pid`=1)