mysql issue: How to get duplicated rows ?

nodi

I have address book and many groups on it
so how can i know the duplicated numbers only ?

groups table schema:

1 - id
2 - name

phonebook table schema:

1 - id
2 - mobile_number
3 - group_id

Please advice

Piranha

If I understand you right you want only the phonenumbers that is in the database more than once. That could be done with the following sql-statement:

$sql = "SELECT pb1.mobile_number as mn 
   FROM phonebook as pb1, phonebood as pb2 
   WHERE pb1.mn = pb2.mn 
   AND pb1.id != pb2.id
   GROUP BY pb1.mn"

Edit: There are better ways to do it, especially if you just want the phonenumber. This way take some time in large databases, it would be better to use some way to count the results. But if you need more than the phonenumbers I think you have to to it this way.

nodi

it's take long time to run ( 170 sec on 10.000 mobile number ! )
🙁

Piranha

I could not remember what the command was, but now I do. Try the following, it doesn't have to join tables, and it will probably be much faster.

$sql = "SELECT mobile_number as mn 
   FROM phonebook as pb
   HAVING COUNT(mn) > 1;"

nodi

it's only return 1 record !

Piranha

Ops, missed one thing. I have now corrected it.

$sql = "SELECT mobile_number as mn 
   FROM phonebook as pb
   GROUP BY mn
   HAVING COUNT(mn) > 1;"

nodi

Error

SQL-query :

SELECT mobile_number AS mn
FROM phonebook AS pb
GROUP BY mn
HAVING COUNT( mn ) >1

MySQL said:
#1054 - Unknown column 'mn' in 'having clause'

Piranha

then just use the "real" names everywhere, don't use AS and the short names

nodi

it's work fine but did't return a correct data 🙁

Piranha

Strange, it should work like a treat. It works without problem in a test database I did.

I can't help you out if it displays wrong data, someone else may be able to help you.