Recods which are in the bigger table and not in a smaller one

[katrina11]

It sounds like a miracle.I created two tables: for hospitalozation and Office visits.Then I merged them by memberid and get 572000 records.
Then it was decided to include alternative id field for further linking to another DW.

For hospitalization table as well as for Office visit I got exactly the same number of records as before and as it is supposed to be,(using the same memberid as before).

However when I merged them as before I got just 571700 records.


I tried to find the "difference" for the investigation

select *
from AcuteTimeFrame_backup
where memberid Not in(select memberid from AcuteTimeFrame_1 )
or
select *
from AcuteTimeFrame_2_backup
where memberAltid Not in(select memberAltid from AcuteTimeFrame_1 )

and I did not get any records.
It looks like there is no diffrence in number of records while there is a difference in number of records!!!

I am certainly doing something wrong...
Is there any other way to get this dissapeared records?

Any help would greatly appreciate.

Thank you!

Katrin

 

[RTag]

Do you have unique key on memberid ? May be you have multiple records per memberid ?

Report automation for Crystal reports and SSRS

http://www.R-Tag.com

 

[gmmastros]

try this:

Select *
From   AcuteTimeFrame_backup
       Left Join AcuteTimeFrame_1
         On AcuteTimeFrame_backup.memberid = AcuteTimeFrame_1.memberid
where  AcuteTimeFrame_1.memberid Is NULL

Select *
From   AcuteTimeFrame_1
       Left Join AcuteTimeFrame_backup
         On AcuteTimeFrame_1.memberid = AcuteTimeFrame_backup.memberid 
where  AcuteTimeFrame_backup.memberid Is NULL


select * 
from   AcuteTimeFrame_2_backup 
       left join AcuteTimeFrame_1
         on AcuteTimeFrame_2_backup.memberAltId = AcuteTimeFrame_1.memberAltId
where  AcuteTimeFrame_1.memberAltId is NULL

select * 
from   AcuteTimeFrame_1
       left join AcuteTimeFrame_2_backup 
         on AcuteTimeFrame_1.memberAltId = AcuteTimeFrame_2_backup.memberAltId 
where  AcuteTimeFrame_2_backup.memberAltId is NULL

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom