连接两个表，从表 1 中获取匹配记录和不匹配记录 [英] Join two tables to get matching records and unmatched records from Table 1

问题描述

场景:我有 2 个表，分别是 Coverage 和 Product

Scenario: I have 2 tables namely Coverage and Product

覆盖表:CId、CName、CType、CMessage、CDate、CoverageProductId

Coverage Table: CId,CName,CType,CMessage,CDate,CoverageProductId

产品表:PId、CName、CType、CMessage、PDate、CoverageProductId

Product Table: PId,CName,CType,CMessage,PDate,CoverageProductId

通过使用 CoverageProductId 我关联了 2 个表.

By using CoverageProductId i am relating 2 tables.

我需要从两个表中获取 3 列 CName、CMessage 和 CoverageProductId

I need to get 3 columns CName,CMessage and CoverageProductId from both tables

条件如下，

• 如果两个表 CoverageProductId 匹配，则从 Product 表中获取 3 列值.
• 如果两个表 CoverageProductId 不匹配，则从 Coverage 表中获取 3 列值.

我的查询在 MS sql server 下面

My query is below in MS sql server

SELECT Distinct C.CoverageProductID
,C.CName
,C.CType
FROM Coverage C
INNER JOIN Product P
ON C.CoverageProductID!=P.CoverageProductID
UNION
SELECT Distinct P.CoverageProductID
,P.CName
,P.CType
FROM Coverage C
INNER JOIN Product P
ON C.CoverageProductID=P.CoverageProductID

但结果与预期不符，返回重复值.

But the result is not as expected and it returns duplicate values.

推荐答案

我认为这可能会满足您的需求:

I think this might do what you want:

select P.CoverageProductID, P.CName, P.CType
from Product P
where exists (select 1
              from coverage c
              where p.CoverageProductID = c.CoverageProductID)
union all
select C.CoverageProductID, C.CName, C.CType
from Coverage C
where not exists (select 1
                  from product p
                  where p.CoverageProductID = c.CoverageProductID);

它从 Product 中获取在 Coverage 中匹配的所有行，然后从 Coverage 中获取在 产品.

It gets all rows from Product that match in Coverage and then all rows from Coverage that don't have a match in Product.

这篇关于连接两个表，从表 1 中获取匹配记录和不匹配记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！