How to compare two different tables

Question

Post reply

How to compare two different tables

dva2007

SSCertifiable

Points: 7707
More actions
June 5, 2008 at 8:51 am

#188718

Hi there,
i m having two tables and both have person id number and mobile number, work number and home number.
There are two tables - Phone1 and Phone2
Phone1
ID Mobile Work Home
1 000 0000
2 090 0909
Phone2
ID Mobile Work Home
1 000 0000
2 090 0990
3 099 0000
Now i need to compare above 2 table and find out how many new mobil e number have been added into phone1. If there is new record then i need to count as 1 but if existing mobile number have been changed then i need to count as existing changed.
so there should be new category that is.
Number added
existing number changed
I am just taking backup of main phone number table every week and compare it to last week backup table of phone number. so that i can make sure how many added during last week since i took the backup.
any help appreciated.
thanks,
vijay

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

What constitutes an ADD? For an existing ID, is the field blank (or null) if there is no entry?

😎

dva2007 SSCertifiable Points: 7707 More actions · Answer 2

Hi,

i m taking copy of existing phone number table every week. So if i take copy this week it is different than last week copy. It might happen that in phone numebr table, some new number have been added and some existing number might have changed in some way.

so when i compare two backup table which is last week and this week then it can give me list of number changed. But i cant say it has been added as ID - 3 is added but ID - 2 has been changed. so i need to make category as ADDED (e.g. 3) and changed (e.g - 2)

thanks,

vijay

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

You haven't completely answered my question. In both ID 1 and ID 2, you have a field that is blank. If that field is NOT blank the next week, is that an ADD or a CHANGE?

😎

mikeg13 SSCommitted Points: 1851 More actions · Answer 4

Not sure of the output you want. For a list of people that have been added...

select ID from Phone2

EXCEPT

select ID from Phone1

An alternate method could be used and would be able to tell you the info that was added, not just the ID.

select ID, Mobile, Work, Home from Phone2

where not exists (select 1 from Phone1 where Phone1.ID = Phone2.ID)

If you want the changes, you can join the tables and compare the values. Here is a quick, untested stab at it. Hopefully it is enough to get you started. It should be modified to only report those with changes and to properly handle nulls.

select Phone1.ID,

case when Phone1.Mobile <> Phone2.Mobile then 'Mobile changed from ' + Phone1.Mobile + ' to ' + Phone2.Mobile end Mobile_Chg,

case when Phone1.Work<> Phone2.Workthen Work changed from ' + Phone1.Work+ ' to ' + Phone2.Work end Work_Chg,

case when Phone1.Home<> Phone2.Home then Home changed from ' + Phone1.Home+ ' to ' + Phone2.Home end Home_Chg

FROM Phone1, Phone2

WHERE Phone1.ID = Phone2.ID

dva2007 SSCertifiable Points: 7707 More actions · Answer 5

Hi,

I dont care about Home number or work number. I do care about mobile number only. When i take backup from main phone number table, i can only those ID which is having at least one mobile number. It still takes those ID where mobile number is blank because home number might have entry of mobiel number. As when people makes entry into the software they r not making sure about number.

thanks

vijay

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Okay, I'm confused about what you are really trying to do. Please provide the DDL of the tables, some sample data (in the form of insert statements), and the expected output from the query you are trying to write.

😎

dva2007 SSCertifiable Points: 7707 More actions · Answer 7

Hi,

Right. i have one table that is phonenumber which is live table. it having id, mobile, home, and work number. Now i need to find out how many person gave only mobile number last week.

The live table will be changed every day. thats obvious.!!!

Now to achieve this, i m taking copy of live table with only mobile number in it and then next week i will take copy of live table with only mobile number and compare that two table. If there are 200 different rows means 200 new mobile number.

But this is not true. Because when i take backup of table, i have to take mobile work and home number all field. As some times in work and home number field contains mobile number (0999090900) etc.

Now say main phone number table as below

Phonenumber

ID Mobile Work Home

1 001 010

2 002 020

3 003 020 030

Now if this table will be changed in such a way that some one will just delete the work number or delete the home number. In that case when i take the backup at that time there will be entry in my table because mobile number is still available. But when i compare two table, it will not match with work number so it will count as changed number. (even though it is different number).

We cant just ignore work and home number column because it might have actual mobile number entered into that column. Thats how people makes entry into front end.

In short, i wanted to find out that in phonenumber table, how many mobile number have been added last week.

I hope it will be still confusing.

thanks,

vijay

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 8

I think you want to say "I hope it is NOT confusing"

It's still unclear to people how you know it's a mobile number if it's in the home or work number field.

If you want to know differences, you can join the tables on ID, and use a WHERE clause that looks for mobiles not matching. If you want to include new ones, potentially, then you would use an outer join.

There is no way that is obvious to determine a mobile number from a home or work number.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Here is an article that you should read: http://www.sqlservercentral.com/articles/Best+Practices/61537/

I am still confused as to what you are trying to do. If you take the time to follow the suggestions in the above article, you will get better help faster.

😎

dva2007 SSCertifiable Points: 7707 More actions · Answer 10

Hi Lynn,

Yes i read it when i started to post my query in this group. I will better do it for this kind of situation because in some cases it is easy to understand from sample data.

thanks for your advice anyways.

regards,

vijay

dva2007 SSCertifiable Points: 7707 More actions · Answer 11

Hi Steve,

it was typing mistake. sorry about it. i m in UK. so i can say when number starts from '07%' or '7%' then it is mobile number only. Home number of work number never starts from 7. so it is easy to find out mobile number from the table even though entry is in home or work number field.

thanks,

vijay