Mysql problem - probably obvious to fix

[Tobman]

Tweet

Hi,

Got a problem that should be simple but I can't get my head around it.

I have a table called offers

And it looks like

retailer 1|red chair|£29.99
retailer 2|red chair|£39.99
retailer 4|red chair|£59.99
retailer 5|red chair|£69.99

retailer 1|blue chair|£69.99
retailer 2|blue chair|£59.99
retailer 4|blue chair|£49.99
retailer 5|blue chair|£59.99

I want to write a query that says:

Select all red chairs where the best price is over £50. I can write a query that brings back:

retailer 4|red chair|£59.99

Easily, but this isn't the best price for this item.

Here's what I've tried, that doesn't do the job:

SELECT product_name, price
FROM offers
WHERE category = 'chairs'
AND price > 50
GROUP BY product_name
ORDER BY price ASC

Thanks!

[scriptmonkey]

From the data you've listed then the query is returning exactly what you asked for, i.e. the lowest price over £50, which is £59.99

I can't see where you'll get a better price (from the data you've listed), have you got any prices between £50 and £59.99?

[D-Mac]

make sure you're not storing the £ symbol in the database, and set the price field to decimal

[Tobman]

Hi Guys, thanks for the posts. I think I've probably explained this the wrong way.

Here's my actual SQL query on this one:

SELECT MIN(o.price_with_delivery_and_voucher), o.merchant_name,
p.page, p.description, p.brand_name, p.product_name, p.specs, p.model
FROM offers o JOIN products p
ON o.product_name = p.product_name
WHERE p.cat1 = 'Washer Dryers'
AND o.display_price > 0
AND o.in_stock = 'true'
AND o.price_with_delivery_and_voucher > 400 GROUP BY o.product_name
ORDER BY o.price_with_delivery_and_voucher ASC LIMIT 0, 10

Here's the problem I'm having.

When I use the GROUP, it groups everything together and I don't want that. I want the lowest price item, but I want the lowest price to be over 400 in this example.

Thanks!

Tom

[Tobman]

Aha! After a frustrating day yesterday, I think I've nailed it with a subquery...

SELECT *
FROM (
SELECT product_name, MIN(price_with_delivery_and_voucher) AS best_price
FROM offers WHERE cat1 = 'Cookers'
AND in_stock = 'true'
AND display_price > 0
GROUP BY product_name
ORDER BY best_price ASC
) AS myalias
WHERE best_price > 500

[scriptmonkey]

Try using 'HAVING' insted, so:

GROUP BY o.price_with_delivery_and_voucher ASC HAVING o.price_with_delivery_and_voucher > 400 LIMIT 0, 10

should replace these:

AND o.price_with_delivery_and_voucher > 400 GROUP BY o.product_name
ORDER BY o.price_with_delivery_and_voucher ASC LIMIT 0, 10