vendredi 31 juillet 2015

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,

  • Get 3 columns values from Product table if both table CoverageProductId matches.
  • Get 3 columns values from Coverage table if both table CoverageProductId not matches.

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.

Aucun commentaire:

Enregistrer un commentaire