how to find all customers with the same first and last name

[rafaelos]

can yoy help my on whats the command to find
all customers form a customer table who have the
same first and last name ?

 

[kiranjasti]

Hi,
If Your Table contains seperate first name and last name columns then the following Query will solve ur problem

SELECT customerid
FROM customertable
WHERE firstnamecolumn = secondnamecolumn;

This will fetch u all the customers who have firstname and secondname same.

If this is not ur requirement then revert back with clear requirement i will give u the solution.

Regards
Kiran Kumar Jasti

 

[bperry]

Kiran,
Or do you mean you want to see records where
the names are duplicated in different rows?
(i.e. more than one John Smith, for example)

 

[Guest_imported]

sorry i didin't make my question clear

i have a customer table and i want to find all customers

in this database who have the same first name and the

same second name but of curse different customercode

which is my primary key

 

[carp]

SELECT first_name, last_name, count(*)
FROM my_table
GROUP BY first_name, last_name
HAVING count(*) > 1;

 

[kiranjasti]

Hi rafaelos,
If Possible can u give ur Table Structure with sample data then we can make u clear about this.

Regards
Kiran

 

[bperry]

The solution provided by <carp> will work well. Sometimes, in addition to seeing the Names, you might also want to see the specific rowids (in this case CustId) of the duplicates. In those cases, you might try either one of the following:

Select DISTINCT A.CustId, A.FName, A.LName
FROM Customers A Inner Join Customers B
ON A.FName = B.FName and A.LName = B.LName
WHERE A.CustId <> B.CustId
ORDER BY A.FName, A.LName, A.CustId

--OR --

Select A.CustId, A.FName, A.LName
From Customers A
Where Exists
(Select B.FName, B.LName From Customers B
WHERE B.FName = A.FName and B.LName = A.LName
GROUP BY B.FName, B.LName
HAVING COUNT(*) > 1)
Order by A.FName, A.LName, A.CustId
----------
brian perry