CODEWITHSUNDEEP
mysql
user3579815
user3579815

Reputation: 572

How to query products where the combined price and quantity is less than another

I have this sample services table where I maintain a records of massage services that I sell as product.

||ID||Duration||Price||
|1  |100      |10.0|
|2  |200      |20.0|
|3  |300      |30.0|
|4  |400      |25.0|
|5  |500      |50.0|

Overtime, I add new products(combination of price and duration) that are more competitive than the existing ones to attract more customers. I want to find outdated records. It is considered outdated when you need pay more and get less. In the above example the record with ID 3 is outdated.

How do I write the query to achieve this?

Upvotes: 2

Views: 67

Answers (1)

Cid
Cid

Reputation: 15247

An outdated massage is a massage that has a price higher than any massage that has a duration higher

The outdated massages can be retrieved using this kind of query :

SELECT m1.*
FROM massages m1
JOIN massages m2
ON m1.Price > m2.Price
AND m1.Duration <= m2.Duration

And then, this query can be used to find the massages to delete in a DELETE query :

Schema (MySQL v5.7)

CREATE TABLE massages (
  `ID` INTEGER,
  `Duration` INTEGER,
  `Price` INTEGER
);

INSERT INTO massages
  (`ID`, `Duration`, `Price`)
VALUES
  (1, 100, 10.0),
  (2, 200, 20.0),
  (3, 300, 30.0),
  (4, 400, 25.0),
  (5, 500, 50.0);

-- -------------------------- This is what you are looking for --------------------------
DELETE FROM massages
WHERE massages.ID IN
(
    SELECT id FROM
    (
      SELECT m1.ID AS id
      FROM massages m1
      JOIN massages m2
      ON m1.Price > m2.Price
      AND m1.Duration <= m2.Duration
    ) AS IdsToDelete
);
-- --------------------------------------------------------------------------------------

Query #1

SELECT * FROM massages;

Output

| ID  | Duration | Price |
| --- | -------- | ----- |
| 1   | 100      | 10    |
| 2   | 200      | 20    |
| 4   | 400      | 25    |
| 5   | 500      | 50    |

View on DB Fiddle

Upvotes: 2

Related Questions