Mismatch In Columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Mismatch In Columns

Author

Topic

ukm_n
Starting Member

5 Posts

Posted - 2012-07-26 : 16:38:59

Hi,

I have two tables, lets say A and B. Both contain the same number of columns and information. The unique keys in both the tables are UserID and OrderID. (Lets say we have 10 columns in both table A and B apart from the primary keys)

I would like to find the columns (in a single row) in table B which are not equal to columns in table A (in a single row).

(Basically I am trying to find data entry errors in table B).

Can someone please help me with the SQL query to accomplish this task.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-26 : 16:53:02

[code]
SELECT b.*
FROM tableB b
INNER JOIN tableA a
On a.OrderID = b.OrderID
AND a.UserID = b.UserID
WHERE a.Col1 <> b.Col1
OR a.Col2 <> b.Col2
...
OR a.Col10 <> b.Col10
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ukm_n
Starting Member

5 Posts

Posted - 2012-07-26 : 16:59:39

Hi,

Thank you for the query, but in table B, I only need the columns where there is a mismatch. For example if Col2 and Col10 in table B have a different value from Col2 and Col10 in table A, then the final result should be UserID, OrderID, Col2, Col10

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-07-26 : 23:28:20

That doesn't make sense. Columns are set by the query definition not the data.
Visakh's query will identify the rows - perhaps you just need to get a little creative with your front end.
What are you trying to do?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-27 : 01:01:28

quote:
Originally posted by ukm_n

Hi,

Thank you for the query, but in table B, I only need the columns where there is a mismatch. For example if Col2 and Col10 in table B have a different value from Col2 and Col10 in table A, then the final result should be UserID, OrderID, Col2, Col10

This is not how you return from the database. you can only return row as a whole for differences. The only other alternative is to show data in below format.


SELECT b.OrderID,b.UserID,
CASE WHEN a.Col1 <> b.Col1 THEN 'Col1 ' ELSE '' END +
CASE WHEN a.Col2 <> b.Col2 THEN 'Col2 ' ELSE '' END +
...
CASE WHEN a.Col10 <> b.Col10 THEN 'Col10 ' ELSE '' END AS ColumnsDiffering
FROM tableB b
INNER JOIN tableA a
On a.OrderID = b.OrderID
AND a.UserID = b.UserID
WHERE a.Col1 <> b.Col1
OR a.Col2 <> b.Col2
...
OR a.Col10 <> b.Col10

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ukm_n
Starting Member

5 Posts

Posted - 2012-07-27 : 11:38:44

Thank you Visakh.

quote:
Originally posted by visakh16

quote:
Originally posted by ukm_n

Hi,

Thank you for the query, but in table B, I only need the columns where there is a mismatch. For example if Col2 and Col10 in table B have a different value from Col2 and Col10 in table A, then the final result should be UserID, OrderID, Col2, Col10

This is not how you return from the database. you can only return row as a whole for differences. The only other alternative is to show data in below format.

SELECT b.OrderID,b.UserID,
CASE WHEN a.Col1 <> b.Col1 THEN 'Col1 ' ELSE '' END +
CASE WHEN a.Col2 <> b.Col2 THEN 'Col2 ' ELSE '' END +
...
CASE WHEN a.Col10 <> b.Col10 THEN 'Col10 ' ELSE '' END AS ColumnsDiffering
FROM tableB b
INNER JOIN tableA a
On a.OrderID = b.OrderID
AND a.UserID = b.UserID
WHERE a.Col1 <> b.Col1
OR a.Col2 <> b.Col2
...
OR a.Col10 <> b.Col10

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-27 : 11:45:42

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources