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.
3条答案
按热度按时间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
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:
yqkkidmi2#
I would identify the common products in an earlier CTE and use it to filter in the UNION ALL CTE.
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: