Simple SQL Question (i think)
-
hi i am tryin to find any duplicates in a person table i have i need to know if anyperson has the same firstName, middleName, LastName and DateOfBirth but i dont really know where to start so im just wondering if anyone has any ideas where how to go about this
-
Do you only want to find where all four items are the same?
SELECT *
FROM tablename
GROUP BY Firstname,
MiddleName
LastName,
DateOfBirth
HAVING FirstName > 1
AND MiddleName > 1
AND LastName > 1
AND DateOfBirth > 1
-SQLBill
-
Nope, that doesn't do it. HAVING needs aggregates to work with.
Join to a derived table that gives the dupes:
Select Distinct t.*
From YourTable As t
Inner Join
(
Select FirstName, MiddleName, LastName, DateOfBirth
From YourTable
Group By FirstName, MiddleName, LastName, DateOfBirth
Having Count(*) > 1
) dt
On (dt.FirstName = t.FirstName And
dt.MiddleName = t.MiddleName And
dt.LastName = t.LastName And
dt.DateOfBirth = t.DateOfBirth )
-
that works thanks alot man
-
A derived table is not needed here. Just run this query
SELECT FirstName, MiddleName, LastName, DateOfBirth
FROM table
GROUP BY Firstname,
MiddleName
LastName
DateOfBirth
HAVING count(*) > 1
-
It's not needed to find the duplicate values, but what PW showed was how to retrieve the rows based on the duplicates in it's entirety.
For that a derived table is the most convenient way.
/Kenneth
-
It still isn't needed. The example below shows a join without a derived table to get whatever info you want. I only checked for dups in the firstname/lastname fields because that is all my table has in it.
SELECT u1.userid, u2.userId, u1.FirstName, u1.LastName
FROM table1 u1 inner join table1 u2 on u1.firstname = u2.firstname and u1.lastname = u2.lastname
and u1.userid <> u2.userid
-
This isn't quite the same thing. It does find dupes alright, but in this case it's also required that you have some additional column(s) to filter against. (userid in this case).
The derived table method does it without needing an external (unique) value to filter against.
/Kenneth
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply