CODEWITHSUNDEEP
sqlsql-servert-sqljoin
ARZ
ARZ

Reputation: 2491

Join statement with different conditions on different row set

I need join two tables using different conditions on different row sets.

For example if RowID < 100 on t1.ColA = t2.ColB and if RowID >= 100 on t1.ColA = t2.ColB+1

I implemented this as below:

... On (RowID <100 and t1.ColA=t2.ColB) OR (RowID >=100 on t1.ColA=t2.ColB+1) ...

But it's very very slow, so what is the problem and what is the better solution?

Upvotes: 1

Views: 100

Answers (2)

Andrey Morozov
Andrey Morozov

Reputation: 7979

You may try this:

On t1.ColA=CASE
WHEN RowID<100 THEN t2.ColB
WHEN RowID>=100 THEN t2.ColB+1
END

or

SELECT
...
CASE
    WHEN RowID<100 THEN t2.Column
    WHEN RowID>=100 THEN t3.Column
END
...
Join t2 On t1.ColA=t2.ColB
Join t2 as t3 On t1.ColA=t3.ColB+1

may be it will be faster

Upvotes: 1

realnumber3012
realnumber3012

Reputation: 1062

RowID <100 on t1.ColA=t2.ColB 

UNION ALL

If RowID >=100 on t1.ColA=t2.ColB+1

Try This solution.

Upvotes: 4

Related Questions