how about something like this, which finds one row (if any) with the
lowest in-stock price, UNIONs it with the highest out-of-stock price (if
any), and then returns just one row, including whether that was an
in-stock or out-of-stock price:
(SELECT col1, price, quantityOnHand, 1 as inStock
FROM products
WHERE col1 = 'Widget3'
AND products.quantityOnHand > 0
ORDER BY products.price asc
LIMIT 1)
UNION
(SELECT col1, price, quantityOnHand, 0 as inStock
FROM products
WHERE col1 = 'Widget3'
AND products.quantityOnHand = 0
ORDER BY products.price desc
LIMIT 1)
ORDER BY inStock desc
LIMIT 1
Here's some data to test with:
CREATE TABLE products (
productsID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
col1 char(10),
price FLOAT NOT NULL DEFAULT 0,
quantityOnHand INT NOT NULL DEFAULT 0 );
INSERT INTO products (col1, price, quantityOnHand)
VALUES ('Widget1', '9.99', 1),
('Widget1', '10.99', 1),
('Widget1', '11.99', 1),
('Widget2', '3.99', 1),
('Widget2', '3.99', 1),
('Widget3', '19.99', 0),
('Widget3', '29.99', 0),
('Widget3', '39.99', 0),
('Widget3', '14.99', 1);
Obviously your structure will be more complex as you'll need to join on
a vendor/supplier table and possibly others.
Kim Christensen wrote:
Hey list;
I have a case where I need to fetch a product row from a table
containing price information about some products, and where every
product can have multiple rows but from different suppliers (thus with
different prices and stock information).
However, I'm trying to create a query which fetches the row matching a
product from the supplier with the best price, but where the stock is
0. If none of the suppliers has the item in stock, it should get the
row with the highest price of them all.
Is this possible with a SQL query, or do I need to fetch all rows for
the product and then process it with my programming language of
choice? :-)
Best Regards
--
Dan Buettner
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
