#1 (permalink)

Hi every one

I have 3 tables + 2 join tables

department department_id, name, description
category category_id, name, description
product product_id, category_id, department_id, name, description

department_category department_id, category_id
department_category_product department_id, category_id, product_id

category belong to more then one department
product belong to more then one category also more then one department

I managed to show categories in selected department.

But I can't come up with sql query to show products that belong to a category that belong to a department.


// retrieves product that belong to the mentioned category
public function GetProductsInCategory($categoryId)
{
$query_string = "SELECT p.product_id, p.name, p.description
FROM product INNER JOIN department_category_product
ON p.product_id = department_categoy_product.product_id
WHERE department_category_product.department_id = 1
AND department_category_product.category_id = $categoryId";
$result = $this->dbManager->DbGetAll($query_string);
return $result;
}

the problem with this query, I have to mention department ID = 1
then I have to write unnecessary query for each department


I tried this
WHERE department_category_product.department_id = department_id
now I am getting all the products that belong in the selected category
AND NOT in the selected department->category

Many, many thanks