Need Help Comparing Data - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Need Help Comparing Data

Author

Topic

WC2015
Starting Member

2 Posts

Posted - 2015-01-02 : 19:16:20

I am trying to compare codes on accounts vs codes on claims. The claims should contain the same info as the account and if not I need to know the difference. I know how to pull the codes from both but I am not sure how to compare them. I can't do a line for line (line=line) because the numbers are not always the same (more lines on one or the other).

Example:

Table 1 -------------Table 2
Line -Code -POA ---Line Code POA
1 --- 123 ---Y -------1 ---123--Y
2 --- 342 ---N -------2 ---342--N
3 --- 200 ---N -------3 ---202--N
4 --- V54
(Ignore the dashes, added to align the tables)

The differences would be lines 3 & 4.

Any help would be very much appreciated.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-03 : 09:53:32

the simplest way to attack this problem is using the set operator EXCEPT"

select * from table1
except
select * from table2

will show you all the rows in table 1 that are not also in table2

reverse the order to show it the other way around

WC2015
Starting Member

2 Posts

Posted - 2015-01-03 : 11:15:03

Thank you for your response.

I have tried using except, and it works, but the problem is I am unable to get the data I need from table 2. Because of this I then need to join additional tables including table 2 which causes the original problem, matching the data.

select *
from table 3
left join table 2 on table 3.id = table 2.id
(
select * from table1
except
select * from table2
) as claim on table 1.id = claim.id

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-03 : 18:49:59

pity you didn't mention you had other tables involved before. I think you're going to have to post some sample data for the tables involved. Then show the results you are getting and the results you want.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources