There are 3 main tables containing information about products and categories.

1) products – there is pid field (internal product id), and cid – primary product's category id
2) categories – cid – this is category id
3) products_categories – contains a relationship between primary and secondary products categories. There are 3 fields: pid – product id, cid– category id and is_primary – indicates if this is primary catagory for product. It is set to 1 when category is same as products.cid; otherwise it is 0.

To select all products from a category run this sql query:
SELECT products.*
FROM products_categories
INNER JOIN products ON products.pid = products_categories.pid
WHERE products_categories.cid = 777
GROUP BY products.product_id;

In this query "777" is category id (cid). Replace it with your category id

If you also would like to select ALL products in NESTED categories of a parent category, you have to change this query just a bit – add in WHERE statement subcategories ids you need:
WHERE products_categories.cid = 777 OR products_categories.cid = 555 OR products_categories.cid = 999 OR products_categories.cid = 111.