Matching Redundant Records - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Matching Redundant Records

Author

Topic

ackweb
Yak Posting Veteran

54 Posts

Posted - 2005-01-29 : 17:57:36

I'm trying to return a list of first and last name combinations which are in the database more than once and indicate how many times they are listed. This particular table includes both Fname, Lname, SpouseFname and SpouseLname in the same record. Regardless of whether a name (e.g. "Bob Jones") is listed as Fname/Lname or SpouseFname/SpouseLname it should be returned by the query and included in the calculated number of matching records. The following code works for matching Fname and Lname, but only SpouseFname and SpouseLname when there is no Fname and Lname in the record. This behavior is probably explained by the CASE statement, but I can't figure out another way to do this. Any suggestions?

SELECT
(CASE ISNULL(Lname,'') WHEN '' THEN SpouseLname ELSE Lname END) AS Lname,
(CASE ISNULL(Fname,'') WHEN '' THEN SpouseFname ELSE Fname END) AS Fname,
COUNT(ID) AS NumRecords

FROM tblPeople
WHERE OrgID = 6 AND
((Lname=Lname AND Fname=Fname) OR (Fname = SpouseFname AND Lname=SpouseLname) OR (SpouseFname = Fname AND SpouseLname=Lname) OR (SpouseFname = SpouseFname AND SpouseLname=SpouseLname))

GROUP BY (CASE ISNULL(Lname,'') WHEN '' THEN SpouseLname ELSE Lname END), (CASE ISNULL(Fname,'') WHEN '' THEN SpouseFname ELSE Fname END)

HAVING COUNT(ID) > 1

ORDER BY Lname

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-29 : 18:16:58

select fname, lname, count(*)
from
(select Fname, Lname from tblPeople
union all
select SpouseFname, SpouseLname from tblPeople
) a
group by Fname, Lname
having count(*) > 1

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ackweb
Yak Posting Veteran

54 Posts

Posted - 2005-01-29 : 18:17:16

I've now figured out how to do this using a @Combined table variable, but would be curious to know whether there is a better way. Thanks!

DECLARE @OrgID int
SET @OrgID = 6

DECLARE @Combined TABLE
(Fname varchar(50),
Lname varchar(50))

INSERT INTO @Combined

SELECT
Fname, Lname

FROM tblPeople

WHERE OrgID = @OrgID AND ISNULL(Fname,'') <> '' AND ISNULL(Lname,'') <> ''

INSERT INTO @Combined

SELECT
SpouseFname, SpouseLname

FROM tblPeople

WHERE OrgID = @OrgID AND ISNULL(SpouseFname,'') <> '' AND ISNULL(SpouseLname,'') <> ''

SELECT
Fname,
Lname,
COUNT(*) AS NumRecords

FROM @Combined
WHERE (Lname=Lname AND Fname=Fname)

GROUP BY Fname, Lname

HAVING COUNT(*) > 1

ORDER BY Lname

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-29 : 18:27:38

See my post above.
Put the filters in and it should give the same result

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ackweb
Yak Posting Veteran

54 Posts

Posted - 2005-01-29 : 18:28:52

This certainly qualifies as a better way of doing this. Thanks "nr"!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources