Need Help Inner Join Problem

[ralph]

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.
I have this query, but it's not working the way I wont.

// 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

[GJay]

Department ID doesn't belong in the second join table, and is probably what's confusing you.

SELECTp.productid, p.name FROM products p JOIN category_product cp ON (cp.productid=p.productid) JOIN category_department cd ON (cd.departmentid=cp.departmentid) WHERE cd.departmentid=1;

[ralph]

Department ID doesn't belong in the second join table, and is probably what's confusing you.

SELECTp.productid, p.name FROM products p JOIN category_product cp ON (cp.productid=p.productid) JOIN category_department cd ON (cd.departmentid=cp.departmentid) WHERE cp.departmentid=1;

Thanks for your time

but now I am more confused,
cp.departmentid=1 there is NO departmentid in cp???

I need to get the product from->category->department
without defining department_id = #1,2,3...

because then I have to dublicate the query for each department #

[GJay]

Sorry, corrected earlier post to edit the typo (cp should have been cd).

Selecting the departmentid along with the productname, and not including it in the 'where' clause might be what you want?

What is it that you're hoping to do with the results? I'm not convinced you're asking for what you actually want... :)

[ralph]

Sorry, corrected earlier post to edit the typo (cp should have been cd).

Selecting the departmentid along with the productname, and not including it in the 'where' clause might be what you want?

What is it that you're hoping to do with the results? I'm not convinced you're asking for what you actually want... :)

yes, I need the departmentID (AND may be categoryID also) with the product.

what I am looking for
when the visitor, select a department the page will show the categories----at this point I am ok
but I have a problem------when select a category will see the PRODUCTS which belong to the selected
category inside selected department.

many thanks again

[GJay]

If you're happy with selecting all the categories for a given department, then what's different about selecting all the products for a given category?

[ralph]

If you're happy with selecting all the categories for a given department, then what's different about selecting all the products for a given category?


because the products in a category belong to more then one department.
let say we have
5 departments
10 categories
100 products

if category_id=1 have 10 products
then

departmentID=1,categoryID=1,productID=1 to 4
departmentID=2,categoryID=1,productID=5 to 6
departmentID=3,categoryID=1,productID=6 to 10

and so on.....


ps. how you said before,, we need some how to pass the dipartment id along with the products name when selecting a category.

Many Thanks To You

[GJay]

Ok, quick example.
You have 'Departments' being Car manufacturers, categories being models, and parts being products.
Now Citroen, Peugeot and Toyota got together last year to build a new shuper-mini. They all gave it different names, but it is the same model.
So you have:

Departments:
1 Citroen
2 Peugeot
3 Toyota

C_D
1 1
1 2
1 3
2 2
3 1

Categories
1 C1/107/Aygo
2 206
3 Yaris

C_P
1 1
1 2
2 1

Products
1 Handbrake B1394
2 Wing Mirror H8388
3 Handbrake H3820

So, as a visitor, I see a list of models on the front page. I happened to buy the Aygo, so choose 'Toyoya'. This presents me with a list of the models toyota make, I choose mine (this is where you've got too so far, yes?). I then get a list of all the products that match my chosen categoryid (the 1st Handbrake, and the wing mirror)- the department id isn't relevant.

As an example of what I think might be confusing you:
We have a furniture store. They have 'Bedroom', 'Lounge', 'Garden' etc. as departments. Both 'Lounge' and 'Garden' might well have categories called 'Chairs', yet a visitor looking under 'Lounge' clearly isn't going to want to be shown white plastic patio furniture. In this case, 'Garden Chairs' and 'Lounge Chairs' would be separate categories, that just happen to have the same name so, again, the department id isn't relevant when selecting products within a category.

[ralph]

Ok, quick example.
You have 'Departments' being Car manufacturers, categories being models, and parts being products.
Now Citroen, Peugeot and Toyota got together last year to build a new shuper-mini. They all gave it different names, but it is the same model.
So you have:

Departments:
1 Citroen
2 Peugeot
3 Toyota

C_D
1 1
1 2
1 3
2 2
3 1

Categories
1 C1/107/Aygo
2 206
3 Yaris

C_P
1 1
1 2
2 1

Products
1 Handbrake B1394
2 Wing Mirror H8388
3 Handbrake H3820

So, as a visitor, I see a list of models on the front page. I happened to buy the Aygo, so choose 'Toyoya'. This presents me with a list of the models toyota make, I choose mine (this is where you've got too so far, yes?). I then get a list of all the products that match my chosen categoryid (the 1st Handbrake, and the wing mirror)- the department id isn't relevant.

As an example of what I think might be confusing you:
We have a furniture store. They have 'Bedroom', 'Lounge', 'Garden' etc. as departments. Both 'Lounge' and 'Garden' might well have categories called 'Chairs', yet a visitor looking under 'Lounge' clearly isn't going to want to be shown white plastic patio furniture. In this case, 'Garden Chairs' and 'Lounge Chairs' would be separate categories, that just happen to have the same name so, again, the department id isn't relevant when selecting products within a category.


God Bless You GJay :)

I got confused by “the same Category name”

Now I make different categoryID with the same name
And with simple query it works

Thanks again GJay :) :)