woensdag 6 september 2017

SQL: Selective joining in the FROM clause

While making  a SQL query I found out that selective joining would be the best solution, but I was not sure about using a CASE WHEN in the FROM clause.

Two simple examples of the tables I used:

tblTermen with some Dutch terms

term
Maat 01
Maat 02
Maat 03
Matrijs

and

tblTermenVertaald with Dutch and English alternatives

nederlands engels
MaatMeasure
Matrix Die
Of course, in the first three cases I only wanted to link the first part of the column form the first table. In the last case all of the field could be used. On the internet I found some indications the CASE WHEN could be used in the FROM clause.

It took a little experimenting but finally I cam up with this:

SELECT term, nederlands, engels
  FROM tblTermen INNER JOIN  tblTermenVertaald ON 
    CASE WHEN CHARINDEX(' ',term)>0 THEN
LEFT(term, CHARINDEX(' ',tblTermen.term)-1)
    ELSE
   tblTermen.term 
    END
    = tblTermenVertaald.nederlands 

and it worked!

term nederlands engels
Maat 01 maat measure
Maat 02 maat measure
Maat 03 maat measure
Matrijs matrijs die



Een reactie plaatsen