Getting Data from 2 identical tables...

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

Getting Data from 2 identical tables...

Author

Topic

laptop545
Starting Member

11 Posts

Posted - 2010-01-13 : 16:50:25

I have two SQL tables, each of which contains the following columns:

userId, name , address , number
userId field is unique

the userIds in both tables are the same, but the rest of the data may not be (e.g. table1 may have Mark as the name while table2 might have Robert)

the rows are not guaranteed to be in the same order in both tables.

1) can anyone tell me how to write a query which will return the userId and the number of matching fields (i.e. if name, address AND number are the same in both tables, it returns 3; if only the name and address is correct but the number is wrong it returns 2; if only the name matches it returns a 1; if only the number matches it returns a 1; etc)

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-13 : 17:50:25

Something like this I would think...

select userId , ([name_match] + [add_match] + [num_match]) as [tot_matches]
from
(
select a.userId
, case when a.[name] = b.[name] then 1 else 0 end as [name_match]
, case when a.[address] = b.[address] then 1 else 0 end as [add_match]
, case when a.number = b.number then 1 else 0 end as [num_match]
from table1 a inner join table2 b on a.userId = b.userId
) a

laptop545
Starting Member

11 Posts

Posted - 2010-01-13 : 18:31:24

Tnx for the help......can we write or design a similar query if the userId is also not guaranteed to be correct? I dont want the entire code..but just how the matching would work ...

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-13 : 19:06:32

You mean like finding the userIds present in tableA and not in tableB ??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-14 : 00:11:42

quote:
Originally posted by laptop545

Tnx for the help......can we write or design a similar query if the userId is also not guaranteed to be correct? I dont want the entire code..but just how the matching would work ...

if userid is not same, on what basis you match the data?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources