CODEWITHSUNDEEP
sqlsql-server
Kevin Liss
Kevin Liss

Reputation: 136

How can I compare 2 tables in MS-SQL?


How can I compare 2 tables with the same rows, but different data?
The tables are something like this:
1. Table price_old:

|-----------------------|
| id | price1 | price2  | 
|-----------------------|
| 1  |   12   |   12    |
|-----------------------|
| 2  |   12   |   55    |
------------------------|
| 3  |   12   |   40    |
-------------------------

The tables are something like this:
2. Table price_old:

|-----------------------|
| id | price1 | price2  | 
|-----------------------|
| 1  |   12   |   12    |
|-----------------------|
| 2  |   13   |   40    |
------------------------|
| 3  |   10   |   40    |
-------------------------

The Result should look like this:
3. Table Result:

|----------------------------------------------------------|
| id  | price1_old | price1_new | price2_old | price2_new  | 
|----------------------------------------------------------|
| 2   |   12       |     13     |      55    |    40       |
|----------------------------------------------------------|
| 3   |   13       |     10     |      40    |    40       |

Upvotes: 1

Views: 103

Answers (2)

Fotis Grigorakis
Fotis Grigorakis

Reputation: 361

This is might be an approach:

SELECT  'TableName' AS `set`, r.*
FROM    robot r
WHERE   ROW(r.col1, r.col2, …) NOT IN
    (
    SELECT  *
    FROM    TableName2
    )
UNION ALL
SELECT  'TableName2' AS `set`, t.*
FROM    tbd_robot t
WHERE   ROW(t.col1, t.col2, …) NOT IN
    (
    SELECT  *
    FROM    TableName1
    )

Upvotes: 1

WJS
WJS

Reputation: 714

Try this, joining on the id and filtering out occurrences where at least one price is different:

SELECT 
old.id,
old.price1 as price1_old,
old.price2 as price2_old,
new.price1 as price1_new,
new.price2 as price2_new
FROM price_old as old
LEFT JOIN price_new as new on old.id=new.id
WHERE old.price1<>new.price1 
OR old.price2<>new.price2

Upvotes: 2

Related Questions