SQL Server Joining two result sets with matching records

ebdffaop  于 2023-02-28  发布在  其他
关注(0)|答案(3)|浏览(162)

The below code is a common table expression. Inside this, we have union all to encapsulate the logic.

WITH CTE_Products
AS
(
SELECT productID, productName, companyName FROM businessContacts
UNION ALL
SELECT productID, productName, companyName FROM nonBusinessContacts
)
SELECT * from CTE_Products where productID > 10

Here, the query is joining the results from two different sources. I want to get only the records from union which productID matches from both the result sets (i.e. businessContacts and nonBusinessContacts). If the product is missing in one of the result set, we should ignore that record all together.

tvokkenx

tvokkenx1#

If the product is missing in one of the result set, we should ignore that record all together.

This can be achieved using an inner join

select b.productID, b.productName, b.companyName
from businessContacts b
inner join nonBusinessContacts n on b.productID = n.productID
where b.productID > 10;

From your comments below the post :

if the other table has no records, we should take the productID from first table

In this case; you have to use left join:

select b.productID, b.productName, b.companyName
from businessContacts b
left outer join nonBusinessContacts n on b.productID = n.productID
where b.productID > 10;
yqkkidmi

yqkkidmi2#

I would identify the common products in an earlier CTE and use it to filter in the UNION ALL CTE.

;WITH Common_Products AS
(
SELECT productID FROM businessContacts
INTERSECT
SELECT productID FROM nonBusinessContacts
)
, CTE_Products AS
(
SELECT productID, productName, companyName FROM businessContacts
WHERE productID IN (SELECT productID FROM Common_Products) 
UNION ALL
SELECT productID, productName, companyName FROM nonBusinessContacts
WHERE productID IN (SELECT productID FROM Common_Products) 
)
SELECT * from CTE_Products where productID > 10
w46czmvw

w46czmvw3#

Making the assumption only the Id values are duplicated in both tables and the other columns contain differing data, perhaps you can simply use an exists correlation:

with CTE_Products
as (
  select productID, productName, companyName 
  from businessContacts b
  where exists (
    select * from nonBusinessContacts n
    where n.productId = b.productId
  )
  union all
  select productID, productName, companyName 
  from nonBusinessContacts n
  where exists (
    select * from businessContacts b 
    where b.productId = n.productId
  )
)
select * 
from CTE_Products 
where productID > 10;

相关问题