Hessel de Walle's Blog

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 Maat Measure 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 ...