SQL to find closest value in Table B to value in Table A-CodePudding

Fairly new to SQL. Thought this would be a simple problem to solve but for some reason solution is eluding me. I have a tblAAmounts of Ingredients and Amounts (in ml) and a tblBScoops of Scoops & ScoopSize (in ml). For each ingredient amount I need closest matching scoop from tblAScoops. E.g. for Blackberries I need 170ml. I don't have a 170ml scoop but closest is a 150ml scoop.

See mock up qryDesiredResult - need SQL that makes this:

It's late, brain=fried and all I've come up with so far is this dog's breakfast (which, of course, doesn't work):

SELECT Ingredient, Amount, ScoopName, ABS(Amount-ScoopSize) AS Diff
FROM tbla_amounts
INNER JOIN tblb_scoops
INNER JOIN (
    SELECT Ingredient AS Ing, ScoopSize as ss, MIN(ABS(ScoopSize-Amount)) AS Aaargh
    FROM tblb_scoops, (
        SELECT Ingredient, Amount FROM tbla_amounts
        WHERE Ing=Ingredient)
        );

Can anyone help me out here?

CodePudding user response：

Running on fumes myself at the moment, but this should work. It tries to grab the first equal or smaller and then also tries to grab the next largest size. Then checks to see which has the smallest difference from the asked for scoop.

SELECT amounts.Ingredient
    , amounts.Amount
    , CASE WHEN lScoop = amounts.Amount OR (amounts.Amount - ISNULL(lScoop.ScoopSize)) < (ISNULL(uScoop.ScoopSize, 0) - amounts.Amount)
                THEN lScoop.ScoopName
           WHEN uScoop.ScoopName IS NOT NULL
                THEN uScoop.ScoopName
            ELSE '0ml' END
FROM tblA_Amounts amounts
LEFT JOIN tblB_Scoops lScoop
    ON lScoop.ScoopSize = (
        SELECT TOP 1 innerScoop.ScoopSize
        FROM tblB_Scoops innerScoop
        WHERE innerScoop.ScoopSize <= amounts.Amount
        ORDER BY innerScoop.ScoopSize DESC
    ) 
LEFT JOIN tblB_Scoops uScoop
    ON uScoop.ScoopSize = (
        SELECT TOP 1 innerScoop.ScoopSize
        FROM tblB_Scoops innerScoop
        WHERE innerScoop.ScoopSize > amounts.Amount
        ORDER BY innerScoop.ScoopSize ASC
    )

CodePudding user response：

Does the following work for you - { } = RDBMS specific adjust as appropriate

select *, (
  select {top (1)} scoopname 
  from scoops 
  order by Abs(amount - scoopsize) 
  {limit 1}
) ScoopToUse
from amounts;

Example DBFiddle