469,167 Members | 1,285 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,167 developers. It's quick & easy.

SQL query

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!
Jul 20 '05 #1
2 4163
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
--
Jul 20 '05 #2
"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
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by jaysonsch | last post: by
9 posts views Thread by netpurpose | last post: by
3 posts views Thread by Harvey | last post: by
4 posts views Thread by Diamondback | last post: by
14 posts views Thread by Dave Thomas | last post: by
4 posts views Thread by Stan | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.