SQL Server JOIN ON值ELSE NULL

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.

我正在努力处理我需要加入数据的查询。我有一张包含各种状态信息的表格。它们有一个failureBitNr,例如“a1_1”。然后我有第二个表将“a1_1”翻译成人类可读的文本(lng_English)。默认情况下,第二个表具有“a1_1”的翻译。但是这也可以被另一个特定的翻译覆盖,例如当failureBitNr用于另一个状态而不是标准时。

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

所以我需要将状态表与failureBitNr上的转换表一起加入。那并不难。

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

但是如何将其加入覆盖基本翻译的翻译呢?

Table 2 looks like this:

表2看起来像这样:

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

注意覆盖现有消息的消息包含非NULL的fileProjectNr。所有标准消息都有一个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.

因此,仅在FailureBitNr上加入将返回两行(905和205)。但我需要加入failureBitNr并做一些不是fileProjectNr的NULL。

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”.

这将返回ID 905,但是如果在failureBitNr a1_1和a1_2上也存在连接,则这些将全部返回为“NULL”而不是“ax_x的标准消息”。

Anyone an idea of how to fix this?

任何人都知道如何解决这个问题?

2 个解决方案

#1

My first idea:

我的第一个想法:

SELECT     t1.*, t2.* FROM     Table1 AS t1 
    LEFT JOIN  Table2 AS t2 (t1.failureBitNr = t2.failureBitNr)
    where t2.fileProjectNr is not NULL
union all               
    SELECT     t1.*, t2.* FROM     Table1 AS t1 
    LEFT JOIN  Table2 AS t2 (t1.failureBitNr = t2.failureBitNr) and 
    where t1.failureBitNr not in (select failureBitNr from Table2 where fileProjectNr is not NULL)

#2

Instead of joining, use UNION ALL and then GROUP BY by failureBitNr and SELECT lng_English by MAX(fileProjectNr) or MAX by fileProjectNr

而不是加入,使用UNION ALL,然后使用GROUPB by failureBitNr和SELECT lng_English by MAX(fileProjectNr)或MAX by fileProjectNr

#1