CODEWITHSUNDEEP
sql-serversql-server-2012
Govind
Govind

Reputation: 979

Join two tables to get matching records and unmatched records from Table 1

Scenario: I have 2 tables namely Coverage and Product

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

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

By using CoverageProductId i am relating 2 tables.

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

Condition Below,

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.

Upvotes: 0

Views: 25673

Answers (2)

Govind
Govind

Reputation: 979

I have tried little better way for same query,

Below is my code,

SELECT 
ISNULL(P.CoverageProductID,C.CoverageProductID),
ISNULL(P.CType,C.CType)
ISNULL(P.CName,C.CName)
FROM   Coverage AS C
           LEFT OUTER JOIN
           Product AS P
           ON C.CoverageProductID = P.CoverageProductID

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

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

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

Upvotes: 2

Related Questions