Query to find 2 cheapest items sum value of which gives 3000-CodePudding

I have a table Products:

ProductName          Price  
straight jeans       1500  
slim jeans           2500  
Denim jacket         3000  
Denim shorts         800  
Skinny jeans         1700  
loose Jeans          2100  
mom Jeans            2800  
wide jeans           1850  
distressed jeans     1100  
bootcut jeans        1350

For purchased two different things with a total value of 3000 or more, they give a third as a gift. I need a SQL query to spend minimum on two things, and take third as the most expensive.

The only thing I've come up with is to go through all possible combinations and find the cheapest combination that over 3000.

WITH Products_sum AS (
  SELECT p1.ProductName AS ProductName1, p2.ProductName AS ProductName2, p1.Price   p2.Price AS TotalPrice
  FROM products p1
  JOIN products p2
    ON p1.ProductName < p2.ProductName
)
SELECT top 1 ProductName1, ProductName2, TotalPrice
FROM Products_sum
WHERE TotalPrice >= 3000
order by TotalPrice asc

I'm expecting answer like:

bootcut jeans        1350
Skinny jeans         1700
Denim jacket         3000

But don't know how to do exactly like that.

CodePudding user response：

I continued from where you left it and compose the expected result.

Converted as cte the top pair of products and the most expensive.

Combine the final resultset with union statement.

WITH Products_sum AS (
  SELECT p1.ProductName AS ProductName1
    , p2.ProductName AS ProductName2
    , p1.Price   p2.Price AS TotalPrice
    , p1.Price Price1
    , p2.Price Price2
  FROM products p1
  JOIN products p2
    ON p1.ProductName < p2.ProductName
),
topProducts as (SELECT top 1 ProductName1, ProductName2, Price1, Price2
FROM Products_sum
WHERE TotalPrice >= 3000
order by TotalPrice asc),
moreExpensive as (
    select top 1 ProductName, Price
    from products 
    order by price desc
)
select productName, Price from(
select 1 as pos, ProductName1 ProductName, Price1 Price from topProducts
union
select 2, ProductName2 , Price2 from topProducts
union
select 3, ProductName, Price
from moreExpensive )q
order by pos

CodePudding user response：

This will give you unique combinations via a CROSS JOIN and a little logic switch.

Example

;with cte as (
Select Distinct
       Prod1  = ProductName
      ,Price1 = Price
      ,Prod2  = ''
      ,Price2  =0
 From YourTable A
 Where PrIce>=3000
Union All
Select Distinct
       Prod1  = case when A.ProductName > B.ProductName then A.ProductName else B.ProductName end
      ,Price1 = case when A.ProductName > B.ProductName then A.Price       else B.Price end
      ,Prod2  = case when A.ProductName > B.ProductName then B.ProductName else A.ProductName end
      ,Price2 = case when A.ProductName > B.ProductName then B.Price       else A.Price end
 From YourTable A
 Cross Join YourTable B
 Where A.Price B.Price>=3000
   and A.ProductName<>B.ProductName
)
Select *
      ,TotalPrice = Price1 Price2
 From  cte
 Order By Price1 Price2

Results

CodePudding user response：

SELECT p1.ProductName, p1.Price,
       p2.ProductName, p2.Price,
       p3.ProductName, p3.Price
FROM Products p1
JOIN Products p2
on p1.ProductName < p2.ProductName
JOIN Products p3
ON p2.ProductName < p3.ProductName AND 
       p1.Price   p2.Price   p3.Price > 3000
LEFT JOIN Products nonexistent
ON NOT (nonexistent.ProductName IN (p1.ProductName, p2.ProductName, p3.ProductName)) AND
   p1.Price   p2.Price   nonexistent.Price > 3000 AND
   nonexistent.Price < p3.Price
WHERE nonexistent.ProductName IS NULL

select every (p1, p2, p3) tuples
where their name differs
their price is greater than 3000
and there doesn't exist any nonexistent record that would differ from these three, would yield a smaller price with p1 and p2 than p3 but still above 3000