Категории и category_path или parent_id

Продукты
  Напитки
    Соки
    Нектары

SELECT MAX(cp.level), c.category_id
FROM oc_category c
JOIN oc_category_path cp ON (cp.category_id = c.category_id)
JOIN oc_product_to_category p2c ON (p2c.category_id = c.category_id AND p2c.product_id = ID_PRODUCT)
GROUP BY c.category_id LIMIT 1

$product_to_category_id = $this->model_catalog_product->getCategoryId($this->request->get['product_id']);

public function getCategoryId($product_id) {
  $query = $this->db->query("SELECT MAX(cp.level), c.category_id FROM " . DB_PREFIX . "category c JOIN " . DB_PREFIX . "category_path cp ON (cp.category_id = c.category_id) JOIN " . DB_PREFIX . "product_to_category p2c ON (p2c.category_id = c.category_id AND p2c.product_id = '" . (int)$product_id . "') GROUP BY c.category_id LIMIT 1");

  return $query->rows;
}

SELECT cp.level max_level, c.category_id
FROM oc_category c
JOIN oc_category_path cp ON (cp.category_id = c.category_id)
JOIN oc_product_to_category p2c ON (p2c.category_id = c.category_id AND p2c.product_id = 42)
ORDER BY max_level DESC
LIMIT 1

SELECT *
FROM oc_category_path cp
JOIN oc_product_to_category p2c ON ( p2c.category_id = cp.category_id
AND p2c.product_id =42 )
ORDER BY level DESC
LIMIT 1