CODEWITHSUNDEEP
sqlsql-server
Dibyanugraha
Dibyanugraha

Reputation: 23

sql - Join two tables with corresponded value

I have a little problem need to be solved. I thought it supposed to be easy at first, but then I couldn't manage to solve it.

I have 2 tables. First table (e.g.) has some data like this

TABLE #1

NAME_1 | VALUE_1
------------
 A     |  2
 B     |  5
 C     |  7
 D     |  12

And the other one has data like this

TABLE #2

NAME_2 | VALUE_2
------------
 AA    |  6
 AB    |  12

I try to have the expected result like this

RESULT

NAME_1 | VALUE_1 | NAME_2
--------------------------
 A     |  2      | AA
 B     |  5      | AA
 C     |  7      | AB
 D     |  12     | AB

As you can see, the result table has a some kind of join condition like

IF table1.value_1 <= table2.value_2
   <show table2.name_2> -->> which suppose to be AA
ELSE table1.value_1 > table2.value_2 AND table1.value_1 <= table2.value_2
   <show table2.name_2> -->> which suppose to be AB

Can someone help me out to solve this problem? Thank you for the attention.

Upvotes: 2

Views: 60

Answers (2)

Tim Lehner
Tim Lehner

Reputation: 15261

You might try a correlated subquery rather than a complicated join condition:

select a.NAME_1, a.VALUE_1,
    (
        select NAME_2 from TABLE_2 where VALUE_2 =
        (
            select min(VALUE_2) from TABLE_2 where VALUE_2 >= a.VALUE_1
        )
    ) as NAME_2
from TABLE_1 as a

First we're finding the VALUE_2 that fits best, then finding the NAME_2 associated with that.

If VALUE_2 is not unique, you may have to use a min on NAME_2.

Upvotes: 3

Eilert Hjelmeseth
Eilert Hjelmeseth

Reputation: 618

It sounds like Table #2 contains a range of values that must join up to Table #1. The below code will build a range in Table #2 (assuming 0 as the minimum possible value) and then join that to Table #1.

WITH [cte_ordered_Table2] AS
    (
        SELECT
            ROW_NUMBER() OVER (ORDER BY [VALUE_2]) [rn],
            [NAME_2],
            [VALUE_2]
        FROM [Table2]
    ),
[cte_ranges] AS
    (
        SELECT
            A.[NAME_2],
            ISNULL(B.[VALUE_2], 0) [min_value],
            A.[VALUE_2] [max_value]
        FROM [cte_ordered_Table2] A
        LEFT JOIN [cte_ordered_Table2] B
        ON      A.[rn] = B.[rn] + 1
    )
SELECT
    A.[NAME_1],
    A.[VALUE_1],
    B.[NAME_2]
FROM [Table1] A
LEFT JOIN [cte_ranges] B
ON      A.[VALUE_1] BETWEEN B.[min_value] AND B.[max_value]

[cte_ordered_Table2] orders the records in Table #2 using the ROW_NUMBER() function so then [cte_ranges] can join to itself to build the range, offsetting by 1 row based on the order.

Upvotes: 0

Related Questions