help selecting

Hi,
I have the following tables, and I would like to filter the categories
to a specific department (and then supplier). Something like an
intersection.
How can I do it? I'm new to SQL, especially with joins, so are the
following 2 joins correct?
1) Filtering to Department can be easy:
SELECT Category.CategoryId, Category.CategoryName FROM Category
JOIN Product ON Product.CategoryId = Category.CategoryId
WHERE Product.DepartmentId = @.DepartmentId
2) Adding a filtering to Supplier and I'm lost...
SELECT Category.CategoryId, Category.CategoryName FROM Category
JOIN Product ON Product.CategoryId = Category.CategoryId
JOIN ProductSupplier ON ProductSupplier.ProductId = Product.ProductId
WHERE (Product.DepartmentId = @.DepartmentId)
AND (ProductSupplier.SupplierId = @.SupplierId)
table Product
ProductId: PK
CategoryId: FK
DepartmentId: FK
table Category
CategoryId: PK
CategoryName
table Department
DepartmentId: PK
table ProductSupplier
ProductId, SupplierId: combined PK
table Supplier
SupplierId
Explanation: a category can be found in one department, and also in
another department, example is category Basin can be in department
Plastic ware and also in department Aluminium ware. Some departments do
not necessarily have a category, example is Department Plastic ware do
not have category Boy short.
If the table are poorly designed, just let me know, I can still change
them now before it's too late.
Thanks for the help--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
I don't believe you should have the DepartmentID in the Products table.
You should have an intersection table for Departments & Products, as you
do in the table ProductSupplier.
CREATE TABLE Product (
ProductID INTEGER NOT NULL PRIMARY KEY ,
CategoryID INTEGER NOT NULL
REFERENCES Category
ON UPDATE CASCADE
)
CREATE TABLE DepartmentProducts (
DepartmentID INTEGER NOT NULL
REFERENCES Departments
ON DELETE CASCADE ,
ProductID INTEGER NOT NULL
REFERENCES Products
ON DELETE CASCADE ,
CONSTRAINT PK_DP PRIMARY KEY (DepartmentID, ProductID)
)
Perhaps:
SELECT C.CategoryId, C.CategoryName
FROM Category As C
INNER JOIN
Product As P ON C.CategorID = P.CategorID
INNER JOIN
ProductSupplier AS PS ON PS.ProductId = P.ProductId
INNER JOIN
DepartmentProducts As DP ON DP.ProductID = P.ProductID
WHERE DP.DepartmentId = @.DepartmentId
AND PS.SupplierId = @.SupplierId
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBRAi3i4echKqOuFEgEQJsCwCg0uoaQoPF+YzN
qSw63TfoergZKDEAnjss
6/qYpDxxxrlemVg9YppKsJIj
=g7sR
--END PGP SIGNATURE--
Michael Wong wrote:
> Hi,
> I have the following tables, and I would like to filter the categories
> to a specific department (and then supplier). Something like an
> intersection.
> How can I do it? I'm new to SQL, especially with joins, so are the
> following 2 joins correct?
> 1) Filtering to Department can be easy:
> SELECT Category.CategoryId, Category.CategoryName FROM Category
> JOIN Product ON Product.CategoryId = Category.CategoryId
> WHERE Product.DepartmentId = @.DepartmentId
> 2) Adding a filtering to Supplier and I'm lost...
> SELECT Category.CategoryId, Category.CategoryName FROM Category
> JOIN Product ON Product.CategoryId = Category.CategoryId
> JOIN ProductSupplier ON ProductSupplier.ProductId = Product.ProductId
> WHERE (Product.DepartmentId = @.DepartmentId)
> AND (ProductSupplier.SupplierId = @.SupplierId)
>
> table Product
> ProductId: PK
> CategoryId: FK
> DepartmentId: FK
> table Category
> CategoryId: PK
> CategoryName
> table Department
> DepartmentId: PK
> table ProductSupplier
> ProductId, SupplierId: combined PK
> table Supplier
> SupplierId
>
> Explanation: a category can be found in one department, and also in
> another department, example is category Basin can be in department
> Plastic ware and also in department Aluminium ware. Some departments do
> not necessarily have a category, example is Department Plastic ware do
> not have category Boy short.
> If the table are poorly designed, just let me know, I can still change
> them now before it's too late.
> Thanks for the help|||Hi MGFoster,
So what you suggest is that a product can be part of multiple departments.
I have initially designed a product to be also part of multiple
categories, but then I simplifed it to be only part of one single
category. Maybe I'll have to think more carefullt about it again.
Thank you for the quick reply.
MGFoster wrote:
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> I don't believe you should have the DepartmentID in the Products table.
> You should have an intersection table for Departments & Products, as you
> do in the table ProductSupplier.
> CREATE TABLE Product (
> ProductID INTEGER NOT NULL PRIMARY KEY ,
> CategoryID INTEGER NOT NULL
> REFERENCES Category
> ON UPDATE CASCADE
> )
> CREATE TABLE DepartmentProducts (
> DepartmentID INTEGER NOT NULL
> REFERENCES Departments
> ON DELETE CASCADE ,
> ProductID INTEGER NOT NULL
> REFERENCES Products
> ON DELETE CASCADE ,
> CONSTRAINT PK_DP PRIMARY KEY (DepartmentID, ProductID)
> )
> Perhaps:
> SELECT C.CategoryId, C.CategoryName
> FROM Category As C
> INNER JOIN
> Product As P ON C.CategorID = P.CategorID
> INNER JOIN
> ProductSupplier AS PS ON PS.ProductId = P.ProductId
> INNER JOIN
> DepartmentProducts As DP ON DP.ProductID = P.ProductID
> WHERE DP.DepartmentId = @.DepartmentId
> AND PS.SupplierId = @.SupplierId
>