Select products by total price efficiently

[Shapper]

Hello,

I have the following tables to which I added some data:

create table dbo.Products
(
Id int identity not null primary key clustered (Id),
Name nvarchar (120) not null,
Price decimal (19,4) not null
);

create table dbo.AllowedQuantities
(
Id int identity not null primary key clustered (Id),
Quantity float not null
);

create table dbo.ProductsAllowedQuantities
(
ProductId int not null,
QuantityId int not null,
constraint ProductIdQuantityId primary key clustered (ProductId, QuantityId)
);

alter table dbo.ProductsAllowedQuantities
add constraint CProductId foreign key (ProductId) references Products(Id),
constraint CQuantityId foreign key (QuantityId) references AllowedQuantities(Id);

insert into dbo.AllowedQuantities (Quantity) VALUES (0.5), (1), (2)

insert into dbo.Products (Name, Price) VALUES ('A', 400), ('B', 500), ('C', 800), ('D', 1000), ('E', 1200), ('F', 1400)

insert into dbo.ProductsAllowedQuantities (ProductId, QuantityId) VALUES (1, 1), (1, 2)
insert into dbo.ProductsAllowedQuantities (ProductId, QuantityId) VALUES (2, 1)
insert into dbo.ProductsAllowedQuantities (ProductId, QuantityId) VALUES (3, 1), (3, 3)
insert into dbo.ProductsAllowedQuantities (ProductId, QuantityId) VALUES (4, 2), (4, 3)
insert into dbo.ProductsAllowedQuantities (ProductId, QuantityId) VALUES (5, 1), (5, 3)
insert into dbo.ProductsAllowedQuantities (ProductId, QuantityId) VALUES (6, 2)

I used the following query:

select p.Name, p.Price, aq.Quantity, TotalPrice = p.Price * aq.Quantity
from dbo.Products as p
join dbo.ProductsAllowedQuantities as paq
on p.Id = paq.ProductId
join dbo.AllowedQuantities aq
on paq.QuantityId = aq.Quantity

And got the following data:

Name - Price - Quantity - TotalPrice
A - 400.0000 - 1 - 400
A - 400.0000 - 2 - 800
B - 500.0000 - 1 - 500
C - 800.0000 - 1 - 800
D - 1000.0000 - 2 - 2000
E - 1200.0000 - 1 - 1200
F - 1400.0000 - 2 - 2800

**NOTE 1**
It is strange that Quantity = 0.5 does not appear ... What am I missing?

**THE OBJECTIVE**

1. Given the total prices TP = { 700, 1900 } I need to find one random product for each one.

2. For each total price given I set a range of +/- 200. So the ranges are:
700 > [500, 900]
1900 > [1700, 2100]

3. Looking at the table I see values that qualifies for these:

700 > [500, 900] > Rows 2 to 4
1900 > [1700, 2100] > Row 5

4. So I would pick one random row of 2 to 4 and row 5.

**NOTE 2**
When possible I would like each selection to contain a different row.

So if the solution would be:

700 > [500, 900] > Rows 2 to 4
1900 > [1700, 2100] > Rows 4 and 5

If row 4 would be selected I would like to be selected only once.

I am not sure if this is possible ...

So I am looking for the most efficiency way to do this.

And to improve my database by changing the scheme if that is necessary or adding some indexes ...

Thank You

[Erland Sommarskog]

Post by Shapper
create table dbo.AllowedQuantities
(
Id int identity not null primary key clustered (Id),
Quantity float not null
);

What's the point with having the quantity in a separate table?

Post by Shapper
**NOTE 1**
It is strange that Quantity = 0.5 does not appear ... What am I missing?

As I said, I don't see the point with the table AllowedQuantities. And you
did indeed go wrong with it.

Post by Shapper
join dbo.AllowedQuantities aq
on paq.QuantityId = aq.Quantity

This should, presumably, be

join dbo.AllowedQuantities aq
on paq.QuantityId = aq.Id

Post by Shapper
700 > [500, 900]
1900 > [1700, 2100]

I am not sure that I understand this entirely, but the simple solution is


DECLARE @TP int = 700

select TOP 1 p.Name, p.Price, aq.Quantity,
TotalPrice = p.Price * aq.Quantity
from dbo.Products as p
join dbo.ProductsAllowedQuantities as paq
on p.Id = paq.ProductId
join dbo.AllowedQuantities aq
on paq.QuantityId = aq.Id
WHERE p.Price * aq.Quantity BETWEEN @TP-200 AND @TP+200
ORDER BY newid()

Although there is only one row that qualifies after I corrected the join.

There is a second posting from you which appears to be the same question.
Is that so? At least I have left it unanswered.