AdamB Posted January 24, 2007 Share Posted January 24, 2007 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!! Link to comment https://forums.phpfreaks.com/topic/35535-mysql-sub-queries/ Share on other sites More sharing options...
fenway Posted January 24, 2007 Share Posted January 24, 2007 I don't see any query that dives into the 2nd level. Link to comment https://forums.phpfreaks.com/topic/35535-mysql-sub-queries/#findComment-168486 Share on other sites More sharing options...
AdamB Posted January 25, 2007 Author Share Posted January 25, 2007 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 :( Link to comment https://forums.phpfreaks.com/topic/35535-mysql-sub-queries/#findComment-169152 Share on other sites More sharing options...
fenway Posted January 25, 2007 Share Posted January 25, 2007 Well, you're simply pulling all products from a given subcategory. Link to comment https://forums.phpfreaks.com/topic/35535-mysql-sub-queries/#findComment-169287 Share on other sites More sharing options...
AdamB Posted January 26, 2007 Author Share Posted January 26, 2007 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... ??? Link to comment https://forums.phpfreaks.com/topic/35535-mysql-sub-queries/#findComment-169946 Share on other sites More sharing options...
fenway Posted January 26, 2007 Share Posted January 26, 2007 Sure there is... but it's usually easier to do with a join per level (provided there are a reasonable number of them). Link to comment https://forums.phpfreaks.com/topic/35535-mysql-sub-queries/#findComment-169952 Share on other sites More sharing options...
AdamB Posted January 26, 2007 Author Share Posted January 26, 2007 Could you possibly point me in the direction of how to modify my above queries to that please? Thank you, Link to comment https://forums.phpfreaks.com/topic/35535-mysql-sub-queries/#findComment-169975 Share on other sites More sharing options...
fenway Posted January 26, 2007 Share Posted January 26, 2007 So let me get this straight... products have categories, and categories can have categories? Link to comment https://forums.phpfreaks.com/topic/35535-mysql-sub-queries/#findComment-170029 Share on other sites More sharing options...
AdamB Posted January 26, 2007 Author Share Posted January 26, 2007 I hope this helps:Master Category - Subcategory - Product - Product - Subcategory - Subcategory - SubSubCategory - Product - ProductThanks for your patience ;D Link to comment https://forums.phpfreaks.com/topic/35535-mysql-sub-queries/#findComment-170051 Share on other sites More sharing options...
fenway Posted January 26, 2007 Share Posted January 26, 2007 So you want all of the categories to each product? How many can there be? Which way do yo uwant to go? Link to comment https://forums.phpfreaks.com/topic/35535-mysql-sub-queries/#findComment-170058 Share on other sites More sharing options...
AdamB Posted January 26, 2007 Author Share Posted January 26, 2007 I know what the Master Category ID is, I just need to select all of the products residing in the "Sub Category" and "SubSubCategory"... Link to comment https://forums.phpfreaks.com/topic/35535-mysql-sub-queries/#findComment-170063 Share on other sites More sharing options...
artacus Posted January 28, 2007 Share Posted January 28, 2007 You might be better writing this one w/o subqueries. Just join the table back on itself Link to comment https://forums.phpfreaks.com/topic/35535-mysql-sub-queries/#findComment-171040 Share on other sites More sharing options...
shoz Posted January 28, 2007 Share Posted January 28, 2007 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 Link to comment https://forums.phpfreaks.com/topic/35535-mysql-sub-queries/#findComment-171126 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.