1

I have read numerous topics and articles and official MYSQL docs but nowhere I have find something that would help me understand how to solve this.

Even this SO topic failed to do what I need (in it, they didn't try to search value bigger then 2, you'll see why that is a problem).

For example I have ranges defined in range_and_prices table:

---

| id  | ranges_from | ranges_to | prices |
| --- | ----------- | --------- | ------ |
| 1   | 1           | 20        | 10     |
| 2   | 21          | 40        | 20     |
| 3   | 41          | 60        | 40

When I try to insert product that have total_quantity 5;

| product_id | product | total_quantity |
| ---------- | ------- | -------------- |
| 1          | Coffee  | 5              |

I get result of my quarry as It should be (as total_quantity is 5, it falls under range of 1-20 and range_prices for that is 10): 

| product_id | product | total_quantity | product_id | range_prices |
| ---------- | ------- | -------------- | ---------- | ------------ |
| 1          | Coffee  | 5              | 1          | 10           |

BUT as soon as I try to get into other ranges I get null for range_prices.

If in fiddle I chose 25 result should be:

| product_id | product | total_quantity | product_id | range_prices |
| ---------- | ------- | -------------- | ---------- | ------------ |
| 1          | Coffee  | 25             | 1          | 20           |

But I am getting null, for anything that is above 20 total_quantity.

View on DB Fiddle

   insert into `range`(`product_id`, `range_prices`) VALUES 
    ((SELECT LAST_INSERT_ID() AS id FROM product ), 
(SELECT prices FROM range_and_prices WHERE 
(SELECT total_quantity FROM product WHERE product_id=id) 
BETWEEN ranges_from AND ranges_to ORDER BY prices ASC
    LIMIT 1 ) );

I believe I know why is that. If I chose for example 25, that number is in between 1 and 40, and result for that is two ranges and it gets confused. I tired all sort of things, like limiting the result, trying to get max and min of ranges, I tried around 20 different things and every time same result. Ether is my logic bad or its to much for me to get my had around. Please help.

Improve this question
4
  • I am just lost on what you are trying to do and what your question is. Commented Apr 6, 2020 at 20:53
  • @GordonLinoff My question is how to construct a query that will put me in right price range based on total_quantity. If total_quantity is 5, result should be 10. If total_quantity is 25 result should be 20, and total_quantity 45, should be 40. As seen in range_and_prices table. Commented Apr 6, 2020 at 20:56
  • @GordonLinoff As you can see in fiddle it works just for first row of ranges. Commented Apr 6, 2020 at 20:58
  • @ikiK . . . More precisely, if I go into the Fiddle, I see that there is a trigger involved. The question seems quite incomplete. Commented Apr 6, 2020 at 21:22

1 Answer 1

Reset to default
1

I had to change your trigger, because it didn't accept a send or third row

As you can see

You can access all new is by using NEW.product_id no need for an select at all

The next thing i needed to change was that i now had no id more so i useed NEW.product:id again.

Schema (MySQL v5.7)

CREATE TABLE `product` (
  `product_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `product` varchar(100) NOT NULL,
  `total_quantity` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `range_and_prices` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `ranges_from` int(11) NOT NULL,
  `ranges_to` int(11) NOT NULL,
  `prices` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `range` (
  `product_id` INT NOT NULL PRIMARY KEY,
  `range_prices` int(11)  NULL,
  FOREIGN KEY (`product_id`) REFERENCES `product`(`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `range_and_prices` (`ranges_from`,`ranges_to`, `prices`) VALUES
('1','20', 10),
('21','40', 20),
('41','60', 40);

CREATE TRIGGER `range_on_product`
AFTER insert ON `product`
FOR EACH ROW
insert into `range`(`product_id`, `range_prices`) VALUES 
(NEW.product_id , (SELECT DISTINCT prices FROM range_and_prices WHERE (SELECT total_quantity FROM product WHERE product_id=NEW.product_id) BETWEEN ranges_from AND ranges_to ORDER BY prices ASC
LIMIT 1 ) );

INSERT INTO `product` ( `product`, `total_quantity`) VALUES ("Coffee", "5"),("sugar", "25");

Query #1

SELECT * FROM `range_and_prices`;

| id  | ranges_from | ranges_to | prices |
| --- | ----------- | --------- | ------ |
| 1   | 1           | 20        | 10     |
| 2   | 21          | 40        | 20     |
| 3   | 41          | 60        | 40     |

Query #2

SELECT * FROM `product`;

| product_id | product | total_quantity |
| ---------- | ------- | -------------- |
| 1          | Coffee  | 5              |
| 2          | sugar   | 25             |

Query #3

SELECT * FROM `product` INNER JOIN `range` ON product.product_id=range.product_id;

| product_id | product | total_quantity | product_id | range_prices |
| ---------- | ------- | -------------- | ---------- | ------------ |
| 1          | Coffee  | 5              | 1          | 10           |
| 2          | sugar   | 25             | 2          | 20           |

View on DB Fiddle

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This is brilliant, I was so close all this time :D Thank you.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.