June 26, 2008 at 7:11 am
Hy. This is my problem. I have 2 tables:
Tab1
Col1 Val
1234 4
345 8
125 9
Tab2
Col2 Val
12 a
123 b
I need to join this 2 tables on Tab1.Col1 like Tab2.Col2+'%'. The problem is that Col1=1234 matches with Col2=12 and Col2=123, but i need that it matches only with Col1=123.
With other words: i need to find only one match and not all matches
Help please 😀
June 26, 2008 at 7:49 am
As long as the values in tab2.col2 are unique, this will return the closest match to tab1.col1. Also, this doesn't return anything for values in tab1.col1 that don't match anything in tab2.col2.
WITH curMax AS (
SELECT col1 AS col,
MAX(LEN(RTRIM(LTRIM(col2)))) AS maxlen
FROM tab1
INNER JOIN tab2
ON col1 LIKE RTRIM(LTRIM(col2)) + '%'
GROUP BY col1
)
SELECT col1,
val1,
col2,
val2
FROM tab1
INNER JOIN tab2
ON col1 LIKE RTRIM(LTRIM(col2)) + '%'
LEFT OUTER JOIN curMax
ON col = col1
WHERE maxlen = LEN(RTRIM(LTRIM(col2)))
June 26, 2008 at 8:04 am
The biggest incovinience is that my tab1 has 1.5 milion rows and tab2 has 1000 rows. I think this joins will take too much time. I would like to use only one select, or something like this
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply