find missing 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)

find missing data

Author

Topic

mayerl
Yak Posting Veteran

95 Posts

Posted - 2012-01-10 : 11:05:36

Good morning,

I have two tables. one has 1038 rows and totals 38,526,810.45 the other table has 1026 rows and amounts to 37,351,168.45. For the life of me I cant make it show me whats different. I've tried left outer joins,right outer joins, not exists, not in, and union. Here's an example. I created the two tables from two views of the data.

select *
from tbl_PLBacklog_test1 t1
	left outer join tbl_backlog_test2 t2
		on t1.amount = t2.amount
		and t1.job = t2.job
		and t1.so_line = t2.so_line

can anyone help me? I'm going crazy here. Thanks in advance.

Laura

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-10 : 11:13:52

why do you expect them to return same totals? are data in them exactly same?

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

mayerl
Yak Posting Veteran

95 Posts

Posted - 2012-01-10 : 11:19:27

I'm told they did until recently. One of the secretaries told me they dont match anymore, about a million dollars difference and I cant see why. The original query has the same tables, the same jons and the same criteria. Very frustrating.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-10 : 11:24:44

then it might be that one of table or tables in query are missing some matching data. Hard to tell where exactly is the problem as we cant see data or have access to your system and also we dont have any idea how they're related.
Best thing in such case is to break it down to a subset where you miss some data and then check each and every table for that criteria to identify which table is culprit

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

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-01-10 : 11:27:26

select a.*, b.*
from MyTable a
FULL OUTER JOIN
MyOtherTable b
on t1.amount = t2.amount
and t1.job = t2.job
and t1.so_line = t2.so_line
where
a.job is null
OR
b.job is null

=================================================
Men shout to avoid listening to one another. -Miguel de Unamuno

mayerl
Yak Posting Veteran

95 Posts

Posted - 2012-01-10 : 11:51:52

thanks to you both. I'll give that a try.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources