|
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
Last edited by raffaell; 04-22-2006 at 11:41 PM..
|
