i will have a table for categories something like id parent_id title so that i can put multiple depth categories like aaa >> bbb >> ccc >> ddd the problem is that when the user clicks aaa the site should show all products in both aaa and its children and grandchildren like in bbb, ccc , ddd etc. i can make this with giving ids that contains parent ids like aaa aaabbb aaabbbccc aaabbbcccddd and use "where category_id like 'aaa%' " but i don't what to use such thing because when i have thousands of products it will take too much time. is there any solution for this with integers etc. Thanks for your help...
Not sure i fyou can do it by the query wihtout some sort of store proc. If you are not using store proc, than you can do it base from code Here is who I would do it, use "where catgeory_id IN ('aaa','bbb','ccc','ddd')" The next part you want is to create a recursive function that takes the parent id that retrieve all children so you can generate the ('aaa','bbb','ccc','ddd') part of the where clause. If I may suggest, using int as ids will probably be more efficient. Also using some sort of table just for mapping out your categories without the title and create indexes with them would increase your query speed.