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]

Re: SQL query

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 ProductSupplier s (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 ProductSupplier s 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 ProductSupplier s
GROUP BY product_id) AS Q
ON P.product_id = Q.product_id
JOIN ProductSupplier s 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]

Re: SQL query

"Chris DiTommaso" <c_dito@yahoo.c om> wrote in message
news:401d375b$0 $70302$75868355 @news.frii.net. ..[color=blue]
> 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![/color]

CREATE TABLE Suppliers
(
supplier_id INT NOT NULL PRIMARY KEY
)

CREATE TABLE Products
(
product_id INT NOT NULL PRIMARY KEY
)

CREATE TABLE ProductsSupplie rs
(
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 ProductsSupplie rs
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_pric e
FROM (SELECT product_id, MIN(product_pri ce) AS product_price
FROM ProductsSupplie rs
GROUP BY product_id
HAVING COUNT(*) > 1) AS P
INNER JOIN
ProductsSupplie rs AS PS
ON PS.product_id = P.product_id AND
PS.product_pric e = P.product_price

Regards,
jag