MySQL Sub-Queries

[AdamB]

Hello,

Im trying to get my head around several levels of subquerying and its driving me insane, i cant figure this out. This code selects what I need it too, all of the sub-categories within the master category.

[code]SELECT categories_id FROM categories WHERE parent_id = 3[/code]

But when I try to then sub-select the sub-sub-categories contained within these results Im only returned with products that reside in the sub category, not the sub-sub-category.

[code]SELECT products_id FROM products WHERE master_categories_id IN (SELECT categories_id FROM categories WHERE parent_id = 3)[/code]

Am I going about this totally the wrong way, or is there something simple Ive forgotten? Thanks for any help!!

[fenway]

I don't see any query that dives into the 2nd level.

[AdamB]

Thats the bit im unsure of, ive had a few tries with combining "IN" clauses but it hasnt worked so I didnt see the point in posting them  :(

[fenway]

Well, you're simply pulling all products from a given subcategory.

[AdamB]

Is there no way to pull all the products from multiple subcategories using one query? It's possible there would only be 1 subcategory with 1 product in, but there could be five subcategories with 5 products in...  ???

[fenway]

Sure there is... but it's usually easier to do with a join per level (provided there are a reasonable number of them).

[AdamB]

Could you possibly point me in the direction of how to modify my above queries to that please? Thank you,

[fenway]

So let me get this straight... products have categories, and categories can have categories?

[AdamB]

I hope this helps:

Master Category
- Subcategory
  - Product
  - Product
- Subcategory
- Subcategory
  - SubSubCategory
  - Product
  - Product

Thanks for your patience  ;D

[fenway]

So you want all of the categories to each product? How many can there be? Which way do yo uwant to go?

[AdamB]

I know what the Master Category ID is, I just need to select all of the products residing in the "Sub Category" and "SubSubCategory"...

[artacus]

You might be better writing this one w/o subqueries. Just join the table back on itself

[shoz]

If you are using PHP or some other language to retrieve the categories, then you may want to try using the approach linked to below as well.

Using the second code snippet you'd change "array((int)$section)" to "array((int)$parentid_variable)"
http://www.phpfreaks.com/forums/index.php/topic,112054.msg454856.html#msg454856

[quote author=fenway link=topic=123849.msg514297#msg514297 date=1169843317]
So you want all of the categories to each product? How many can there be? Which way do yo uwant to go?
[/quote]
[quote author=AdamB link=topic=123849.msg514302#msg514302 date=1169843444]
I know what the Master Category ID is, I just need to select all of the products residing in the "Sub Category" and "SubSubCategory"...
[/quote]

I don't know if the "..." in your previous post means that the sub categories are unlimited but what's being asked for is the limit, if any, to the number of subcategories that you allow or are looking to retrieve. An appropriate JOIN can then be constructed to retrieve the ids.

The following article should make things clearer and also show another approach for storing the data.
http://dev.mysql.com/tech-resources/articles/hierarchical-data.html