Finding who bought three or more of the most expensive flower available, in a single order

Question

I would like to rewrite this SQL. However, I need it to exclude the sql in() operator and the limit clause.

The query will output the emailAddress, orderID and quantity. It is searching for customers who have bought three or more of the most expensive flower available, in a single order.

I'm not too sure how to do this, any help would be greatly appreciated.

SELECT customer.emailAddress, custOrder.orderId, flowerPurchase.quantity
FROM flowerPurchase
INNER JOIN custOrder ON flowerPurchase.orderId = custOrder.orderId
INNER JOIN customer on customer.customerId = custOrder.customerId
WHERE flowerID IN(  
        SELECT flowerID
        FROM flower
        ORDER BY unitPrice DESC
        LIMIT 1
    )
AND quantity >= 3

score 0 · Answer 1 · answered Apr 21 '23 at 11:41

If you want to get rid of IN and LIMIT, then you could

SELECT customer.emailAddress, custOrder.orderId, flowerPurchase.quantity
  FROM flowerPurchase
       INNER JOIN custOrder ON flowerPurchase.orderId = custOrder.orderId
       INNER JOIN customer ON customer.customerId = custOrder.customerId

this:

 WHERE     flowerID = (SELECT MAX (flowerID)
                         FROM flower
                        WHERE unitPrice = (SELECT MAX (unitPrice) FROM flower))
       AND quantity >= 3;

because it selects one (max flowerID, which is what limit 1 does anyway) flower that costs the most (max of unitPrice)

Finding who bought three or more of the most expensive flower available, in a single order

1 Answers1