If you need to select all products, that match some specific category, try this request:
SELECT p.product_id, p.product_name, p.subsubcategory_id FROM category c
JOIN subcategory sc ON sc.subcategory_category_id = c.category_id
JOIN subsubcategory ssc ON ssc.subsubcategory_subcategory_id = sc.subcategory_id
JOIN product p ON p.subsubcategory_id = ssc.subsubcategory_id
WHERE c.category_id = 1;
But you should think about changing your database structure to make your requests faster and simpler.
Edit: Answering the comment about how to improve DB.
Current design of database looks correct, according to actual data relations. 1-many for cat-subcat and 1-many for subcat-subsubcat. But this leads to complicated (and possibly slow) queries while usage.
One way I see is to implement many-many relation with additional restriction. You can create additional table cat-subcat, just as you would do if you needed many-many. But in that table you can set unique limitation to subcat_id, so every subcat could belong only to 1 cat and it becomes in fact 1-many relation. But in this case you can move both up- and downwards the hierarchy. This approach will reduce number of JOINs in your query only by 1, but the whole logic of the query would be easier to understand.
Another way. As I understand this is the query for web-store filter. So, new products will be inserted much more seldom, than viewed by category. You can just add subcat_id and cat_id fields to your product, which is not good idea from the point of data structure, but for this particular situation this might be good solution. Every time new product is inserted to DB, you should control the correctness of those 2 fields by PHP or whatever you use on server. But when products are searched by category you will have simple request without JOINs at all.
Both approaches are based on the idea to sacrifice some space for speeding up and simplifying the queries, that are frequently used. Maybe there is even better solution, but I can't find it right now.
