Join tables but allow use of records once only

Question

CREATE TABLE #A (UpperLimit NUMERIC(4))
CREATE TABLE #B (Id NUMERIC(4), Amount NUMERIC(4))

INSERT INTO #A VALUES 
    (1000), (2000), (3000)
INSERT INTO #B VALUES 
    (1, 3100), 
    (2, 1900), 
    (3, 1800), 
    (4, 1700), 
    (5, 900), 
    (6, 800)

Given these 2 tables, I want to join Table A to B ON B.Amount < A.UpperLimit but each record from Table B can only be used once, so the desired output would be:

I could easily do this by plopping Table B's records into a temp table, cursor over table A taking top record < UpperLimit and Deleting that record from the temp table or some other programmatic solution, but I'd like to avoid that and I'm pretty sure this could be done with a "normal" (recursive CTE? Partition?) query.

Oneal

Answer 1

You could achieve your desired output using below recursive CTE

WITH 
DATA AS
(
  SELECT * FROM #A A1 INNER JOIN #B B1 ON A1.UpperLimit >= B1.Amount
),
MA AS
(
  SELECT MIN(UpperLimit) AS MinLimit, MAX(UpperLimit) AS MaxLimit FROM #A
),
RESULT AS 
(
  -- Get the first record corresponding with maximum upper limit  
  SELECT * 
  FROM DATA D1
  WHERE NOT EXISTS 
        (SELECT 1 
        FROM DATA D2 
        WHERE D2.UpperLimit = D1.UpperLimit AND D2.Amount > D1.Amount)
        AND D1.UpperLimit = (SELECT MaxLimit FROM MA)
  
  -- Recursive get remain record corresponding with other upper limit 
  UNION ALL      
  SELECT D1.* 
  FROM RESULT R1 INNER JOIN DATA D1 
       ON (R1.UpperLimit > D1.UpperLimit AND R1.Id != D1.Id) 
  WHERE D1.UpperLimit >= (SELECT MinLimit FROM MA)
       AND NOT EXISTS 
        (SELECT 1 
        FROM DATA D2 
        WHERE D2.UpperLimit = D1.UpperLimit AND D2.Amount > D1.Amount AND D2.Id != R1.Id)   
)

SELECT DISTINCT * FROM RESULT ORDER BY UpperLimit DESC;

Demo: https://dbfiddle.uk/Y-m0K6Mk

Thordis

Answer 2

Might be a bit lengthy but hopefully clear enough.

with a as
    (select -- order and number rows in table A in some way
        row_number() over (order by UpperLimit) as RnA,
        *
        from #a),
b as
    (select -- order and number rows in table B in the same way
        row_number() over (order by Amount) as RnB,
        *
        from #b),
m as
    (select -- get and number all possible pairs of values from both tables considering the restriction
        row_number() over (order by a.UpperLimit desc, b.Amount desc) as RnM,
        *
        from a
            join b on
                b.Amount < a.UpperLimit),
r as
    (select -- use recursion to get all possible combinations of the value pairs with metrics of interest for comparison
        convert(varchar(max), RnA) as ListA,
        convert(varchar(max), RnB) as ListB,
        RnA,
        RnB,
        1 as CountB,
        convert(int, Amount) as SumB
        from m
        where RnM = 1
    union all
    select
        r.ListA + ' ' + convert(varchar(max), m.RnA),
        r.ListB + ' ' + convert(varchar(max), m.RnB),
        m.RnA,
        m.RnB,
        r.CountB + 1,
        r.SumB + convert(int, m.Amount)
        from m
            join r on
                m.RnA < r.RnA and
                m.RnB < r.RnB),
e as
    (select top(1) -- select combinations of interest using metrics
        ListA,
        ListB
        from r 
        order by CountB desc, SumB desc),
ea as
    (select -- turn id list into table for table A
        ea.Rn,
        ea.Value
        from e
            cross apply(select row_number() over (order by (select null)) as Rn, Value from string_split(e.ListA, ' ')) as ea),
eb as
    (select -- turn id list into table for table B
        eb.Rn,
        eb.Value
        from e
            cross apply(select row_number() over (order by (select null)) as Rn, Value from string_split(e.ListB, ' ')) as eb)
select -- get output table with actual values from the original tables
    a.UpperLimit,
    b.Amount,
    b.Id
    from ea
        join eb on
            ea.Rn = eb.Rn
        join a on
            ea.Value = a.RnA
        join b on
            eb.Value = b.RnB;

Yclept

Thanks for the answer - it does work, but I think Trung's answer is a bit cleaner. – Oneal

Answer 3

You can use an APPLY with a TOP 1 for this. Each row in the outer table gets only one row from the APPLY.

SELECT
  *
FROM #A a
OUTER APPLY (
    SELECT TOP (1) *
    FROM #B b
    WHERE b.Amount < a.UpperLimit
) b;

To simulate an inner-join (rather than a left-join) use CROSS APPLY.

Rudolphrudwik

When I run your query I get ID 2 showing for both the 3000 and 2000 limit; I don't know the best way to describe it but I don't want any values from Table B being used to fulfill the join more than once, so it'd be like TOP(1) EXCEPT(IN RESULTS ALREADY) – Oneal

I see, sorry misunderstood. What do you want to happen if there is no available row from B? Or do you want to just see the row in A that is higher than each one in B? (In other words the reverse of the above) – Rudolphrudwik

In my case, there'll always be records available in B that are lower than the limits specified in A so I'm happy with handling that however is easiest? – Oneal

And no, not reverse of the above. I want to see one unique record from B for each A, if that makes any sense? Ty in advance and for first attempt :) – Oneal

Answer 4

This query returns very close to desired outcome.

WITH CTE AS (SELECT B.*,
                    ROW_NUMBER() OVER (PARTITION BY B.Value ORDER BY B.Value DESC) AS RowNum
             FROM #B B),
     cc as (SELECT A.Limit, CTE.*
            FROM #A A
                     LEFT JOIN CTE ON CTE.Value < A.Limit AND CTE.RowNum = 1),
     cc2 as (select *, MAX(Value) OVER ( PARTITION BY cc.Limit) as l1 from cc)
select Limit, ID, Value
from cc2
where Value = l1

This query use 3 Common Table Expressions. First sort Table B with ROW_NUMBER() function and PARTITION BY clause, second one JOIN Table A with Table B with the condition given and the third one filters the record that is in Limit on Table A and use the Limit only once.

Toxicosis