Please suggest query

Hi,
(php and mysql)
Below are 2 tables I got

Categories:
catid parent
1 0
2 0
3 1 (sub cat of 1)
4 1 (sub cat of 1)

Products:
prodid catid
1 1
2 1
3 2
4 4


Here's the problem.
I'm getting the "catid" from a form. These catid's are only of categories which have parent set to 0. So form may send 1 or 2 to script

Now I got to select all "prodid" from "products" table where catid is either the number sent by form, AND also "prodid" where it's a sub cat of number sent by form.

So if I get "1" from form, my query should be able to select:

products:
1 1
2 1
4 4 (4 is a sub cat of 1 in "categories")

What should be the query?
Thank you

 

I recommend that you create your tables in MS Access - then create a query in MS Access - it should be reasonably easy - you'll get exactly what you are looking for!

 

I don't know how to make tables and queries in ms-access... I'm using php and mysql...

I can get the sub categories listed in an array, and then get "prodid" for each, but that would slow the script and increase queries...
Thanks

 

Something like this.

select p.prodid from products p where p.catid=$catid or p.catid in (select catid from Categories where parent=$catid)

PHP:

 

Hi,
Thanks that worked I think. Green reputation added.