Joining table with range ID matching

Question

I currently have 2 tables which needs to be joined.

The first table is a big table,millions of records. The second is a matching table. I would want to join the 2 table. A table of the intended result is shown below. I would need tips on how to write the SQL to join the 2 tables.

    Table 1

    ID X   Column X
    1      X1
    2      X2
    3      X3
    4      X4
    5      X5
    6      X6
    ...  ...
    the list goes on

    Table 2

    ID   Column Y
    1    Y1
    3    Y2
    6    Y3
    11   Y4


    Intended result

    ID X   Column X  Column Y
    1      X1        Y1
    2      X2        Y1
    3      X3        Y2
    4      X4        Y2
    5      X5        Y2
    6      X6        Y3
    7      X7        Y3
    8      X8        Y3
    9      X9        Y3
    10     X10       Y3
    11     X11       Y4
    12     X12       Y4

I dont understand how you could join these tables and expect a result like ` 2 X2 Y1` because no record exists in [table 2] to join [id] 2, so the result would be ` 2 X2 NULL`, on a left outer join. and the result wouldnt exist on an inner join. — , Jan 23 '14 at 09:14
He wants to match everything whose ID is in the range between two rows in table 2. See my answer for how to do it. — Barmar, Jan 23 '14 at 09:16

score 4 · Accepted Answer · answered Jan 23 '14 at 09:12

4

SELECT idX, columnX, columnY
FROM (
    SELECT id, columnY, @prevID AS prevID, @prevID := id
    FROM table2
    CROSS JOIN (SELECT @prevID := NULL) init
    ORDER BY id DESC) AS t2
JOIN table1 AS t1 ON t1.idX >= t2.id AND (t2.prevID IS NULL OR t1.idX < t2.prevID)

DEMO

answered Jan 23 '14 at 09:12

Barmar

741,623
53
500
612

This is actually working in mySQL? How is the performance for a larger number of rows as this must process serially like a cursor? – dnoeth Jan 23 '14 at 09:29
It processes table 2 serially, but the join with table 1 can use an index on idX. – Barmar Jan 23 '14 at 09:31
Hi Barmar, but when i run this on MS-SQL. It returns me Incorrect syntax near ':'. for the @prevID := id – Louis Jan 23 '14 at 09:52
This answer is for MySQL, I don't know MS-SQL. – Barmar Jan 23 '14 at 09:53

score -1 · Answer 2 · edited Jan 23 '14 at 09:14

-1

SELECT T1.IDX, T1.ColumnX , T2.ColumnY FROM TABLE1 T1
INNER JOIN TABLE2 T2 ON T1.IDX = T2.ID

edited Jan 23 '14 at 09:14

Barmar

741,623
53
500
612

answered Jan 23 '14 at 09:11

Scott

19
2
6

This won't work for the IDs in table 1 that aren't in table 2. – Barmar Jan 23 '14 at 09:15

score -1 · Answer 3 · edited May 23 '17 at 12:03

I guess to get the intended result you need to use inner join as:

select t1.id, t1.X, t2.Y from table1 t1 inner join table2 t2 on t1.id = t2.id;

Userful Links:

http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

Full Outer Join in MySQL

Edited:

Oh I didn't check that rows don't match. I tried this way. It worked for me:

select t1.id, t1.x,
if(t2.y != 'null',t2.y,(select y from table2 t3 where t3.id < t1.id order by t3.id desc limit 1)) as 'y'
from table1 t1 left join table2 t2 on t1.id = t2.id;

This isn't matching on the range between IDs in table 2. – Barmar Jan 23 '14 at 09:17 — Barmar, Jan 23 '14 at 09:17

Joining table with range ID matching

3 Answers3