SQL JOIN:当存在匹配项时，从 LEFT 和 RIGHT 表中返回行

Question

我有一个 #tmp 表，我需要从左表和右表中查找具有匹配名称和覆盖范围的所有记录。

-- select * from #tmp
--#tmp table
ID  Name  IsConverted  Coverage  EarliestPolicyEffectiveDate
1   abc   1            Test1     9/1/2017
2   abc   0            Test1     9/2/2017
3   abc   0            Auto      9/3/2017
4   xyz   0            Home      9/3/2017
-- select * from #tmp where IsConverted = 0
--LEFT TABLE
ID  Name  IsConverted  Coverage
2   abc   0            Test1
3   abc   0            Auto
4   xyz   0            Home
-- select * from #tmp where IsConverted = 1
--RIGHT TABLE
ID  Name  IsConverted  Coverage
1   abc   1            Test1
-- DESIRED RESULTS
ID  Name  IsConverted  Coverage
1   abc   1            Test1
2   abc   0            Test1
-- CURRENT RESULTS
ID  Name  IsConverted  Coverage
2   abc   0            Test1

select *
from 
    (SELECT * FROM #tmp tmp WHERE tmp.IsConverted = 0) nc
join
    (SELECT * FROM #tmp tmp WHERE tmp.IsConverted = 1) ic on ic.Name = nc.Name and ic.Coverage = nc.Coverage

我需要能够从左右两个表中获取匹配的记录。这样做的原因非常复杂，没有为这篇文章添加任何其他信息。

我已经尝试了完全外部连接，交叉应用，外部应用。没有任何效果。

编辑：

最好我想使用 JOIN，因为一旦找到匹配的结果，我仍然需要访问左表和右表，因为我需要确保 LEFT。最早保单生效日期在权利后 15 天内。最早保单生效日期 .

我不确定如果我做 UNION ALL 我能做到这一点。

如下例所示：

select *
from 
    (SELECT * FROM #tmp tmp WHERE tmp.IsConverted = 0) nc
join
    (SELECT * FROM #tmp tmp WHERE tmp.IsConverted = 1) ic on ic.Name = nc.Name and ic.Coverage = nc.Coverage
where 
    nc.EarliestPolicyEffectiveDate between DATEADD(d, -15, ic.EarliestPolicyEffectiveDate) and DATEADD(d, 15, ic.EarliestPolicyEffectiveDate)

Answer 1

我认为

有更好的方法可以做到这一点，但UNION ALL和EXISTS似乎在这里有效：

SELECT *
FROM #tmp t
WHERE IsConverted = 1 
AND EXISTS(SELECT 1 FROM #tmp
           WHERE IsConverted = 0
           AND Name = t.Name
           AND Coverage = t.Coverage)
UNION ALL
SELECT *
FROM #tmp t
WHERE IsConverted = 0
AND EXISTS(SELECT 1 FROM #tmp
           WHERE IsConverted = 1
           AND Name = t.Name
           AND Coverage = t.Coverage);

Answer 2

这对我有用。 有一个子查询来获取 LEFT 和 RIGHT 之间的所有匹配

名称。 和一个子查询，以获取 LEFT 和 RIGHT 之间的所有匹配覆盖率。 然后使用"AND"在WHERE子句中将它们链接在一起

select * from #tmp
where name in (Select LEF.Name from LEF 
                INNER JOIN RIG ON LEF.NAME = RIG.NAME) 
        and coverage in (Select LEF.coverage from LEF 
                INNER JOIN RIG ON LEF.coverage = RIG.coverage)

结果：

ID          NAME       IsConverted Coverage   EarliestPolicyEffectiveDate
----------- ---------- ----------- ---------- ---------------------------
1           abc        1           Test1      2017-09-01
2           abc        0           Test1      2017-09-02

Answer 3

我想我现在看到您正在尝试将左表直接连接到右表，而不是使用 #tmp 作为中间连接。

您需要将两者相交的地方联合起来。

伪代码：

SELECT * FROM LEFT TABLE WHERE EXISTS(matching row in RIGHT TABLE)
UNION 
SELECT * FROM RIGHT TABLE WHERE EXISTS(matching row in LEFT TABLE)