Sybase Joining Table with one column modified

Question

Table1:
Col1 | Col2 | Col3
 a1  | b1   | c1
 a2  | b1   | c2
 a3  | b2   | c3

Table2:
Col1 | Col2 | Col3
 a1  | y1   | z1
 a2  | y3   | z2
 a3  | y3   | z3

The thing is that b1 actually corresponds to y1, however they can't be compared as they aren't same. Same is true for b2, b3. You can refer the below code to understand what I mean.

Now I want to apply Inner Join here on two tables using Col1 and Col2 from both.

I tried this code but it doesn't work.

SELECT Col1,
        (CASE Col2
        WHEN 'ENXTPAR_ID' THEN 'XPAR'
        WHEN 'ENXTAMS_ID' THEN 'XAMS'
        WHEN 'ENXTLIS_ID' THEN 'ENXL'
        ELSE Col2
        END) as Col2,
        Col3
FROM Table1
INNER JOIN Table2
ON Table1.Col1= convert(varchar,Table2.Col1)
AND Table1.Col2 = Table2.Col2

I hope the problem is clear.

score 1 · Accepted Answer · answered Feb 23 '18 at 06:45

I think the CASE expression needs to be in the ON condition, not in the select:

SELECT t1.*
FROM Table1 t1
INNER JOIN Table2 t2
    ON t1.Col1 = CONVERT(varchar, t2.Col1) AND
       t2.Col2 = CASE t1.Col2
                 WHEN 'ENXTPAR_ID' THEN 'XPAR'
                 WHEN 'ENXTAMS_ID' THEN 'XAMS'
                 WHEN 'ENXTLIS_ID' THEN 'ENXL'
                 ELSE t1.Col2 END;

The above logic assumes that you want to compare the Table2.Col2 column against a modification of the Table1.Col2 column. For example, if Table1 has ENXTPAR_ID, then a match would occur with Table2 and the value XPAR. If I have the columns backwards, then you can easily switch around the CASE expression.

Of course, the best long term solution here is to setup join columns which can map through equality, and maybe even add indices where appropriate. But, sometimes we are stuck we other people's data sets and we have to cope with that.

Sybase Joining Table with one column modified

1 Answers1