I am trying to compare data from two tables: Source A and Source B

The query that I am performing is as follows:

SELECT      * 
FROM        (
            SELECT SourceA_Name, SourceA_Value
            FROM SourceA
            GROUP BY SourceA_Name, SourceA_Value
            ) t1
INNER JOIN  (
            SELECT SourceB_Name, SourceB_Value
            FROM SourceB
            GROUP BY SourceB_Name, SourceB_Value
            ) t2
ON          t1.SourceA_Name = t2.SourceB_Name AND t1.SourceA_Value = t2.SourceB_Value

The output of the query above is as follows:

As seen above, the output if the query is not comparing the data as expected. Although the values may exist in both Source A and Source B, the output of the query returns the matching values on separate rows.

I am expecting the following output:

I would appreciate any assistance on this.

CodePudding user response：

SELECT * FROM SourceA
EXCEPT 
SELECT * FROM SourceB

CodePudding user response：

You can try this query. It should work.

SELECT t.*
FROM (SELECT sa.SourceA_Name, sa.SourceA_Value, sb.SourceB_Name, sb.SourceB_Value,
             case when sa.SourceA_Name = sb.SourceB_Name 
                   and sa.SourceA_Value = sb.SourceB_Value then 'Yes' else 'No' end as Is_match
        FROM SourceA sa
        JOIN SourceB sb
          ON sa.SourceA_Name = sb.SourceB_Name AND sa.SourceA_Value = sa.SourceB_Value) t

--if you want to select only records for which there is a match you can use WHERE

where t.is_match = 'Yes'