How to join the table based on condition in join keys-CodePudding

I have Two table, and I want to join them, but the join key is depend on the condition. For example when table2 column2 value is not represented in table1 start values, I want to join the on the next value. For example, table2 column2 value 9 is not in start value, and I want to merge on value 10 (next possible value) from start columns.

    select * from table1 left join table2 on table2.column2=table1.start or 
table2.column2=table1.start 1 or table2.column2=table1.start 2 .. until I find merge value from start columns

CodePudding user response：

You can use the LAG analytic function to find the previous start in table1 and then join between the previous and current values:

SELECT t1.col1,
       t1."START",
       t2.*
FROM   ( SELECT t.*,
                LAG("START") OVER (ORDER BY "START") AS prev
         FROM   table1 t
       ) t1
       LEFT OUTER JOIN table2 t2
       ON ((t1.prev IS NULL OR t1.prev < t2.col2) AND t2.col2 <= t1."START")

Which, for the sample data:

CREATE TABLE table1 (col1, "START") AS
SELECT 'a', 10 FROM DUAL UNION ALL
SELECT 'v', 11 FROM DUAL UNION ALL
SELECT 'b', 13 FROM DUAL UNION ALL
SELECT 'r', 14 FROM DUAL;

CREATE TABLE table2 (col1, col2) AS
SELECT 'a',  9 FROM DUAL UNION ALL
SELECT 'q', 10 FROM DUAL UNION ALL
SELECT 's', 11 FROM DUAL UNION ALL
SELECT 'd', 13 FROM DUAL;

Outputs:

COL1	START	COL1	COL2
a	10	a	9
a	10	q	10
v	11	s	11
b	13	d	13
r	14	null	null

fiddle

CodePudding user response：

DECLARE @Table1 AS TABLE (col1 char(1), starting int) DECLARE @Table2 AS TABLE (column1 char(1), column2 int)

INSERT INTO @Table1 (col1, starting) VALUES('a', 10), ('v', 11), ('b', 13), ('r', 14),('a', 11) INSERT INTO @Table2 (column1, column2) VALUES('a', 9), ('q', 10), ('s', 11), ('d', 13)

SELECT a.column1, MIN(a.Col2) FROM @Table1 t1 OUTER APPLY (SELECT t2.column1, (case when t2.column2 < t1.starting then t1.starting else t2.column2 end) Col2 FROM @Table2 t2 WHERE t2.column1 = t1.col1) a GROUP BY a.column1