"Chris DiTommaso" <c_****@yahoo.com> wrote in message
news:40***********************@news.frii.net...
I have a table similar to the northwind products table.
The difference is that I have products that may be supplied by multiple
supplier. (i.e. there may be two or more rows that have the same product
name, with different supplier,price,etc.)
I need the following queries:
1. Show rows of all products that only have one supplier.
2. Show rows of all products that have multiple suppliers, but only
show the 'preferred' supplier. (preferred is determined by lower
purchase price)
Thanks
dito
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
CREATE TABLE Suppliers
(
supplier_id INT NOT NULL PRIMARY KEY
)
CREATE TABLE Products
(
product_id INT NOT NULL PRIMARY KEY
)
CREATE TABLE ProductsSuppliers
(
product_id INT NOT NULL REFERENCES Products (product_id),
supplier_id INT NOT NULL REFERENCES Suppliers (supplier_id),
product_price DECIMAL (8, 2) NOT NULL CHECK (product_price > 0),
PRIMARY KEY (product_id, supplier_id)
)
-- All products with only one supplier
SELECT product_id, MIN(supplier_id) AS supplier_id
FROM ProductsSuppliers
GROUP BY product_id
HAVING COUNT(*) = 1
-- All products who have multiple suppliers with their cheapest supplier
SELECT PS.product_id, PS.supplier_id, PS.product_price
FROM (SELECT product_id, MIN(product_price) AS product_price
FROM ProductsSuppliers
GROUP BY product_id
HAVING COUNT(*) > 1) AS P
INNER JOIN
ProductsSuppliers AS PS
ON PS.product_id = P.product_id AND
PS.product_price = P.product_price
Regards,
jag