T-SQL左连接使用“或”操作符

I have the following query where I would like to pull in either or both on a match. There can be more than one ID or HIC for a member. So if the member ID = member ID then pull the max (loaddate) for the most current HIC Likewise if HIC = HIC pull in the member ID with the max (loaddate). I want to include the left join in case a match isn't found for either scenario.

我有下面的查询，我想在匹配中选择一个或两个。一个成员可以有多个ID或HIC。因此，如果成员ID =成员ID，那么为当前HIC拉出最大(loaddate)，同样，如果HIC = HIC用最大(loaddate)拉入成员ID。我想要包含左连接，以防在任何一种情况下都找不到匹配。

Code:

代码:

 SELECT 
    CH1.*
    ,AVM.MBR_ID
    ,AVM.HIC
    ,AVM.MBR_LST_NM
    ,AVM.MBR_FST_NM
    ,CAST(AVM.MBR_BIRTH_DT AS DATE) AS 'MBR_BIRTH_DT'
    ,AVM.LOADDATE
FROM 
    #CHECK1 CH1
LEFT JOIN 
    AVRIL.DBO.VW_MBR AVM ON (CH1.HICN = AVM.HIC OR CH1.MEMBER_ID = AVM.MBR_ID)
WHERE 
    CH1.HEALTH_PLAN = 'AVRIL' 
    AND AVM.LOADDATE=(SELECT MAX(LOADDATE) FROM AVRIL.DBO.VW_MBR)

Thanks, Michael

谢谢你,迈克尔

2 个解决方案

#1

When you use a field from the VW_MBR table in the where clause, it effectively turns the left join into an inner join. Put the condition in the join:

当您在where子句中使用来自VW_MBR表的字段时，它会有效地将左连接转换为内部连接。将条件放在连接中:

...
FROM #CHECK1 CH1
    LEFT JOIN AVRIL.DBO.VW_MBR AVM ON (CH1.HICN = AVM.HIC OR CH1.MEMBER_ID = AVM.MBR_ID)
      AND AVM.LOADDATE=(SELECT MAX(LOADDATE) FROM AVRIL.DBO.VW_MBR)
WHERE CH1.HEALTH_PLAN = 'AVRIL'

#2

Here may be an alternative to the first proposed answer, I'm using a custom query as jointure in order to optimize it (I use only the max load dates instead of trying the jointure on every rows of the table).

这里可能是第一个建议答案的替代方案，我使用自定义查询作为jointure，以便对它进行优化(我只使用最大负载日期，而不是在表的每一行上尝试jointure)。

SELECT CH1.*
    ,MAVM.MBR_ID
    ,MAVM.HIC
    ,MAVM.MAX_LOADDATE
FROM #CHECK1 CH1
LEFT JOIN (SELECT AVM.HIC
               ,AVM.MBR_ID
               MAX(AVM.LOADDATE) AS [MAX_LOADDATE]
           FROM AVRIL.DBO.VW_MBR AVM
           GROUP BY AVM.HIC, AVM.MBR_ID) MAVM (MAVM.HIC = CH1.HICN
                                                  OR MAVM.MBR_ID = CH1.MEMBER_ID)
WHERE CH1.HEALTH_PLAN = 'AVRIL'

I simplified the query to focus on the changes in comparison with the other proposed query. You can still add an INNER JOIN clause to AVRIL.DBO.VM_MBR in order to get additional columns:

我将查询简化为关注与其他建议的查询相比的更改。您仍然可以向AVRIL.DBO添加内连接子句。VM_MBR以便获得更多的列:

...
LEFT JOIN (...) MAVM ...
INNER JOIN AVRIL.DBO.VW_MBR AVM2 ON AVM2.HIC = MAVM.HIC
                                   AND AVM2.MBR_ID = MAVM.MBR_ID
                                   AND AVM2.LOADDATE = MAVM.MAX_LOADDATE
...

So you can use AVM2.xxx for the columns you want to use.

你可以用AVM2。xxx对于您想要使用的列。

Hope this will help

希望这将帮助

#1