Selecting values between two columns in multiple rows

Question

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.

I am just lost on what you are trying to do and what your question is. — Gordon Linoff
– Gordon Linoff, 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. — ikiK
– ikiK, Commented Apr 6, 2020 at 20:56
@GordonLinoff As you can see in fiddle it works just for first row of ranges. — ikiK
– ikiK, 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. — Gordon Linoff
– Gordon Linoff, Commented Apr 6, 2020 at 21:22

nbk · Accepted Answer · 2020-04-06 21:16:26Z

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

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

Collectives™ on Stack Overflow

Selecting values between two columns in multiple rows

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related