CODEWITHSUNDEEP
sqlsql-servert-sql
karthiK
karthiK

Reputation: 17

Query to find the mismatched records in two tables without a primary key

I want to select the mismatched records from two tables (shown below) using a full outer join and a where clause (query written below). Both the tables don't have a primary key. I think because of the outer join, I am getting all the 3 records in the result. Is there a way to return only the records that have been mismatched by modifying the query? (i.e 3rd record).

table1:

enter image description here

table2:

enter image description here

Query Used:

    DECLARE @table1 TABLE  (element_type varchar(100),element_code  varchar(100), value varchar(100))
    DECLARE @table2 TABLE  (element_type varchar(100),element_code  varchar(100), value varchar(100))
    
    Insert into @table1
    values
    ('ABC', 'CODE1', 'Interest Due'),
    ('ABC', 'CODE1', NULL),
    ('BCD', 'CODE2', 'value1')
    
    Insert into @table2
    values
    ('ABC', 'CODE1', 'Interest Due'),
    ('ABC', 'CODE1', NULL),
    ('BCD', 'CODE2', 'value2')
    
    SELECT  
            [Element Type] = ISNULL(m1.element_type, m2.element_type),
            [Differences]  = 'value missmatch'
        FROM    
            @table1 m1 
            FULL OUTER JOIN @table2 m2 
                ON m1.element_type = m2.element_type AND m1.element_code = m2.element_code
        WHERE
        ISNULL(m1.value, '') <> ISNULL(m2.value, '')

Output:

enter image description here

Desired Output:

enter image description here

Upvotes: 0

Views: 1031

Answers (3)

persian-theme
persian-theme

Reputation: 6638

Your question was not clear, but the query below produces the output you want.

SELECT  
       [Element Type] = m1.element_type,
           [Differences]  = 'value missmatch'
       FROM    
            @table1 m1 
            Left JOIN @table2 m2 
                ON m1.element_type = m2.element_type AND m1.element_code = m2.element_code AND m1.value != m2.value
WHERE m1.value IS NOT NULL AND m2.value IS NOT NULL

Upvotes: 0

id&#39;7238
id&#39;7238

Reputation: 2593

Set Operators - EXCEPT and INTERSECT

WITH result AS (
  (SELECT * FROM @table1 UNION SELECT * FROM @table2)
  EXCEPT
  (SELECT * FROM @table1 INTERSECT SELECT * FROM @table2)
)
SELECT DISTINCT element_type, element_code, 'value missmatch' AS Differences
FROM result;

fiddle

Upvotes: 2

Serg
Serg

Reputation: 22811

Include all columns into ON clause respecting nullability

SELECT  distinct
        [Element Type] = ISNULL(m1.element_type, m2.element_type),
        [Differences]  = 'value missmatch'
    FROM    
        table1 m1 
        FULL OUTER JOIN table2 m2 
            ON m1.element_type = m2.element_type 
               AND m1.element_code = m2.element_code 
               AND (m1.value = m2.value or m1.value IS NULL and m2.value IS NULL)
    WHERE m1.element_type IS NULL OR m2.element_type IS NULL

Upvotes: 1

Related Questions