Comapring data from 2 tables - 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 2000 Forums

Transact-SQL (2000)

Comapring data from 2 tables

Author

Topic

vandyits
Starting Member

2 Posts

Posted - 2006-12-04 : 11:31:25

I have a problem. I have two tables of serial numbers, one from accounting and one from a physical audit. I'm trying to reconcile these together and I need to find the number of matching characters from one table refrencing the other table.

table1.snumber table2.snumber = number of characters that match so that I don't have to go through each line of each table and do this manually.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-04 : 11:37:50

select isnull(pa.snumber, a.snumber) snumber,
case when pa.snumber is null then 'Only in accounting table'
when a.snumber is null then 'Only in physical audit table'
else 'Hey! Here is a match...' end Result
from <physical audit table name here> pa
full join <accounting table name here> a on a.snumber = pa.snumber

Peter Larsson
Helsingborg, Sweden

vandyits
Starting Member

2 Posts

Posted - 2006-12-04 : 12:12:58

what i'm really trying to account for is the typo's between to two tables. I want to know how many characters are the same in the a.snumber field and pa.snumuber field to find the ones that are very similar and try to reconcile them.

X002548
Not Just a Number

15586 Posts

Posted - 2006-12-04 : 12:27:00

This should float your boat


USE AdventureWorks
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 char(10))
CREATE TABLE myTable00(Col1 char(10))
GO

INSERT INTO myTable99(Col1)
SELECT '1234567890' UNION ALL
SELECT 'ZZZ4567890' UNION ALL
SELECT '1234567ZZZ'

INSERT INTO myTable00(Col1)
SELECT '1234567890' UNION ALL
SELECT 'XXX4567890' UNION ALL
SELECT 'YYY4567ZZZ'
GO

SELECT Source, Col1 FROM (
			SELECT Col1, 'MyTable99' AS Source FROM myTable99 UNION ALL
			SELECT Col1, 'MyTable00' AS Source FROM myTable00) AS YYY
WHERE Col1 IN (
SELECT Col1 FROM (
	SELECT Col1
	  FROM myTable99
 UNION ALL
	SELECT Col1
	  FROM myTable00) AS XXX
GROUP BY Col1
HAVING COUNT(*) = 1)
GO

SET NOCOUNT OFF
DROP TABLE myTable99, myTable00
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources