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
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
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 |
Reacties