SQL query

[Chris DiTommaso]

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!

[David Portas]

Since you have a many-to-many relationship between Suppliers and Products
you should normalise your data by creating a joining table. The correct
design would look something like this:

CREATE TABLE Products (product_id INTEGER PRIMARY KEY, product_name
VARCHAR(20) NOT NULL UNIQUE)

CREATE TABLE Suppliers (supplier_id INTEGER PRIMARY KEY, supplier_name
VARCHAR(20) NOT NULL UNIQUE)

CREATE TABLE ProductSuppliers (product_id INTEGER NOT NULL REFERENCES
Products (product_id), supplier_id INTEGER NOT NULL REFERENCES Suppliers
(supplier_id), PRIMARY KEY (product_id, supplier_id), price NUMERIC(10,2)
NOT NULL CHECK (price>0))

Products which only have a single supplier:

SELECT P.product_id, P.product_name,
MIN(S.supplier_id) AS supplier_id,
MIN(S.supplier_name) AS supplier_name,
MIN(Q.price) AS price
FROM Products AS P
JOIN ProductSuppliers AS Q
ON P.product_id = Q.product_id
JOIN Suppliers AS S
ON Q.supplier_id = S.supplier_id
GROUP BY P.product_id, P.product_name
HAVING COUNT(*)=1

Product with preferred supplier. I assume it's possible for more than one
supplier to offer the same lowest price on a product. This query gives all
suppliers with the lowest price.

SELECT P.product_id, P.product_name,
S.supplier_id, S.supplier_name, Q.price
FROM Products AS P
JOIN
(SELECT product_id, MIN(price) AS price
FROM ProductSuppliers
GROUP BY product_id) AS Q
ON P.product_id = Q.product_id
JOIN ProductSuppliers AS R
ON P.product_id = R.product_id AND Q.price = R.price
JOIN Suppliers AS S
ON R.supplier_id = S.supplier_id

--
David Portas
SQL Server MVP
--

[John Gilson]

"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