Find out if records are missing

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

Find out if records are missing

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-06-28 : 11:32:00

I want to compare two tables:

DC

RecTbl (this is the master table).

I want to make certain the info in the RecNum Field in the DC table are also in the RecTbl. How would I write this query?

The fields in both tables are:

RecNum char(9)
LAF char (2)
BIC char (2)

Thanks

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-06-28 : 11:54:01

Never mind this worked for me:

SELECT *
FROM DC D
left outer join RecTbl RT
on D.RecNum = RT.RecNum
WHERE RT.RecNum IS NULL

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-06-30 : 07:45:30

Missing 43 records from RecTbl

I used this to get the records out but the table DC has 28449 records in that table and only 28406 in the RecTbl

SELECT *
FROM DC D
left outer join RecTbl RT
on D.RecNum = RT.RecNum
WHERE RT.RecNum IS NULL

How can I find the missing records 43 records from the RecTbl Table?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-30 : 07:50:41

Your query gives 43 RecNum values from table DC.
These 43 RecNum values are missing in table RecTbl.
What is your problem now?

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-06-30 : 07:55:43

Table DC has 28449 records

Table RecTbl has 28404 records

Table RecTbl is the master table. So How can I find the different 43 records between the two?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-30 : 08:03:56

Maybe I am stupid but I still believe that your posted query will show you exactly that records

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-06-30 : 08:12:23

No you're not stupid it's coming out the same for me too I thought I was missing something. Okay thanks anyway!

Sachin.Nand

2937 Posts

Posted - 2010-06-30 : 08:16:04

Not quite sure but what happens if you use Full Outer Join?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-30 : 08:17:12

So you only want to check it in a different way to be sure?

select * from DC as d
where not exists(select * from RecTbl r where r.RecNum = d.RecNum)

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-06-30 : 08:28:28

Thanks... tried that and got the same results. Okay I feel better now just wanted to try it another way.

Thanks for your help.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-30 : 08:30:24