I’m struggling with a query where I need to join data.
I have a table with all kinds of status messages. These have a failureBitNr like for example “a1\_1”. Then I have a second table that translates “a1\_1” to a human readable text (lng\_English). By default, the second table has a translation for “a1\_1”. But this can also be overwritten by another specific translation, for example when the failureBitNr is used for another status than standard.

So I need to join the status table with the translation table on failureBitNr. That’s not that hard.

But how do I join it on the translation that overwrites the basic translation?

Table 2 looks like this:

```
id   fileProjectNr MachineNr failureBitNr  lng_English
905  2203456       2         a6_1          This message overwrites the standard
205  NULL          NULL      a6_1          Standard message for a6_1
204  NULL          NULL      a1_1          Standard message for A1_1
203  NULL          NULL      a1_2          Standard message for A1_2

```

Remark that the message that overwrites the existing one holds a fileProjectNr that is not NULL. All standard messages have a fileProjectNr NULL

So joining only on FailureBitNr will return both rows (905 & 205). But I need to join on failureBitNr and do something with not being NULL of fileProjectNr.

So I did this:

```
DECLARE @ProjectNr int = 123456

SELECT
    t1.*,
t2.*
FROM
    Table1 AS t1
LEFT JOIN
    Table2 AS t2
ON
(t1.failureBitNr = t2.failureBitNr)
AND
    (t2.fileProjectNr LIKE
    CASE WHEN t2.fileProjectNr = @ProjectNr THEN
        @ProjectNr
    ELSE
        NULL
    END
)
WHERE
    {where statement}

```

This will return ID 905, however if there is also a join on failureBitNr a1\_1 and a1\_2, these will all be returned as “NULL” in stead of “Standard message for ax\_x”.

Anyone an idea of how to fix this?