March 12, 2007 at 12:38 pm
Hi All.
I am trying to query for duplicate records. The fields that I am comparing is coming from two diferent different tables.
Example:
Table : Member
DOB SSN Phone
01/02/2007 123456789 281-098-1234
01/02/2007 123456789 713-098-1234
06-02-1964 987654321 817-098-6754
Table: Entity
firstname lastname City
John Smith Phoenix
John Smith Phoenix
Paul Harris Dallas
I need to compare on fields DOB, SSN, firstname and last name. I can bring out the duplicates on each table when I query like this:
Step 1:
select
* from member x
join
(
select
dob, ssn
from
member group by dob, ssn
having
count (*) > 1)y
on
x.dob=y.dob and x.ssn=y.ssn
Step 2:
select
* from entity a
join(
select
firstname,lastname
from
entity group by firstname,lastname
having
count (*) > 1)b
on
a.firstname=b.firstname and a.lastname=b.lastname
But the query messes up when I join the two tables ( it is a legit join) to compare on all the fields.
I am very new to T-SQL. Can you advise on what I am doing wrong or I need to do ?
Thank You.
March 12, 2007 at 1:57 pm
How are you joining the 2 tables? There doesn't appear to be a common data element.
March 12, 2007 at 2:06 pm
Forgot to mention. There is a common key, say member id in both tables.
March 12, 2007 at 3:19 pm
Anybody ?
March 13, 2007 at 7:37 am
I think your problem was probably an ambiguous column name error, but I'm guessing on limited info!
I think this will help you, but make sure my assumptions are correct. If not, please give me some more info on your tables and their relationships.
--==============================================
-- Assumptions:
-- 1 - member_id is unique to each record in
-- entity and member
-- 2 - entity.member_id is foreign-keyed
-- to member; in other words, each
-- member_id in entity exists in member.
--
-- Modifications:
-- 1 - In the duplicates joins, only pull fields
-- from original table (you don't need to
-- show results of sub-queries, right?)
-- 2 - Join [entity] and [member] on [member_id]
--==============================================
select
x.*, a.*
from member x
join entity a
on x.member_id = a.member_id
-- Find duplicates in member
join ( select dob, ssn
from member
group by dob, ssn
having count (*) > 1) y
on x.dob = y.dob
and x.ssn = y.ssn
-- Find duplicates in entity
join ( select firstname, lastname
from entity
group by firstname, lastname
having count (*) > 1) b
on a.firstname = b.firstname
and a.lastname = b.lastname
--==============================================
Carter B.
Hope that helped, but let me know if it didn't!
But boss, why must the urgent always take precedence over the important?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply