SQL JOIN：匹配时返回LEFT和RIGHT表中的行

I have a #tmp table, and I need to find all records from the LEFT table and from the RIGHT table that have a matching Name and Coverage.

我有一个#tmp表，我需要找到LEFT表和RIGHT表中具有匹配的Name和Coverage的所有记录。

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

I need to be able to get the matching records from both tables, left and right. The reason for this is very complicated and does not add any additional information for this post.

我需要能够从左右两个表中获取匹配的记录。原因很复杂，并没有为此帖添加任何其他信息。

I have tried FULL OUTER JOIN, CROSS APPLY, OUTER APPLY. Nothing is working.

我尝试过FULL OUTER JOIN，CROSS APPLY，OUTER APPLY。没有什么工作。

EDIT:

编辑：

Preferably I would like to use JOIN because once I find the matching results, I still need access to the left table and the right table because I need to make sure the LEFT.EarliestPolicyEffectiveDate within 15 days of RIGHT.EarliestPolicyEffectiveDate .

我最好使用JOIN，因为一旦找到匹配的结果，我仍然需要访问左表和右表，因为我需要在RIGHT.EarliestPolicyEffectiveDate的15天内确保LEFT.EarliestPolicyEffectiveDate。

I'm not sure I can do that if I do UNION ALL.

如果我做UNION ALL，我不确定我能做到。

Like below example:

如下例所示：

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)

3 个解决方案

#1

I think there are better ways to do this, but UNION ALL and EXISTS seems to work here:

我认为有更好的方法可以做到这一点，但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);

#2

This works for me. have a subquery to get all the matching names between LEFT and RIGHT. and a subquery to get all matching coverage between LEFT AND RIGHT. then use 'AND' to link them together in the WHERE clause

这对我有用。有一个子查询来获取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)

result:

结果：

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

#3

I think I see now that you are trying to join the LEFT TABLE directly to the RIGHT TABLE, not using #tmp as a middle join.

我想我现在看到你试图将LEFT TABLE直接加入到RIGHT TABLE中，而不是使用#tmp作为中间连接。

You need to UNION the two where they intersect.

你需要将两个相交的地方联合起来。

Psuedocode:

伪代码：

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

#1