Missing records... - 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

Missing records...

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-05-12 : 09:44:15

My two tables both have the same field names. The table names are:

Diaries and Test

In Diaries I have 141 records. In Test I have 131 records. I want to compare the two tables so I can see which records I'm missing so I can fix my stored procedure. How do I go about writing this?

The field names in both tables are:
doc, totmet, totclr, weekdat, dowrdat

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-12 : 10:02:03

Wait until Charlie is coming up because I will go and get a coffee

No, you're never too old to Yak'n'Roll if you're too young to die.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-05-12 : 10:04:27

Okay thanks. I just want to see the records that are missing from the Diaries Table.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-12 : 10:17:06

select *
from Diaries d
LEFT join Test t
on t.doc = d.doc
and t.totmet = d.totmet
and d.totclr = d.totclr
and d.weekdat = d.weekdat
and d.dowrdat = t.dowrdat
where t.doc is null

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-12 : 10:26:51

Also:-


select d.* 
from Diaries d
WHERE NOT EXISTS(SELECT 1 FROM Test t
WHERE t.doc = d.doc
and t.totmet = d.totmet
and t.totclr = d.totclr
and t.weekdat = d.weekdat
and t.dowrdat = d.dowrdat
)

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-12 : 10:27:46

quote:
Originally posted by webfred

select *
from Diaries d
LEFT join Test t
on t.doc = d.doc
and t.totmet = d.totmet
and d. t.totclr = d.totclr
and d. t.weekdat = d.weekdat
and d.dowrdat = t.dowrdat
where t.doc is null

No, you're never too old to Yak'n'Roll if you're too young to die.

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

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-05-12 : 10:34:18

THANKS!!!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-12 : 10:36:21

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources