Need Help Joining 3 tables
 

Hi every one

I have 3 tables

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

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

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
Jovani

Re: Need Help Joining 3 tables
 

I'll give u 3 hints but i can't go through ur requirements and solve it for you since it would take a few hours for me to do it.

1)draw ur erd and figure out the relationships between the tables, ie 1 to 1, 1 to many or many to many.

2)think subselect

3)an alternate to the join keyword is the where keyword.