Differences between fields of two tables

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

Differences between fields of two tables

Author

Topic

nnogueira
Starting Member

18 Posts

Posted - 2011-01-26 : 17:15:09

Hello friends.

Would you like a little help to help identify differences between similar tables.

Data Example:

table1
CustomerID OrderID ProductID
1500 155 200
1500 155 233
1501 157 150
1501 157 361

table2
CustomerID OrderID ProductID
1500 155 200
1500 156 200
1500 156 301
1501 157 150
1501 157 361

The expected result of this query should be:

CustomerID OrderID ProductID
1500 156 233 --- In table1 But not have this combination in table2 for CustomerID 1500 (OrderID is different from table1 to table2 for the same customer)

1502 158 200 --- In table2 But not have this combination in table1 for CustomerID 1502 (CustomerID is declared in table2 but not in table1)

Well, it would be something like join the two tables and set the fields to compare, verify them what is the difference between them. Any ideas?

Thanks

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-01-26 : 19:52:04

quote:

1500 156 233 --- In table1 But not have this combination in table2 for CustomerID 1500 (OrderID is different from table1 to table2 for the same customer)

1502 158 200 --- In table2 But not have this combination in table1 for CustomerID 1502 (CustomerID is declared in table2 but not in table1)

Well, it would be something like join the two tables and set the fields to compare, verify them what is the difference between them. Any ideas?

That is the solution.

nnogueira
Starting Member

18 Posts

Posted - 2011-01-27 : 08:41:42

Thanks for reply Namman.

I`ve seek for many ways to do it, do you have a example command ?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2011-01-27 : 08:54:27

starter hint...expand to meet your requirements.

select * from table1 a
left join table2 on a.col1 = b.col1
where b.col1 is null

union all

select * from table2 a
left join table1 on a.col1 = b.col1
where b.col1 is null

nnogueira
Starting Member

18 Posts

Posted - 2011-01-27 : 10:44:56

Thanks for reply AndrewMurphy, i will try this and other kind of similar JOIN´s.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources