display the duplicated rows only !

php1999

Hi all,

I have a table which has my address book

ID , NAME , GROUP_ID , MOBILE

how can i know the duplicated rows for the same user in different groups so I can decide to delete it manually or not.

regards

laserlight

I do not really understand what you mean, especially since "delete it manually" is rather vague.

What are the relevant tables (e.g., group table)? What exactly do you want to do?

Also, a many to many relationship is typically expressed with a mapping table. This does not seem to be the case here... so are you actually duplicating the data in the users table?

illzz

You can try (INNER) joining the table to itself where the names are the same, but the groups don't match.

SELECT columns FROM table t1
   INNER JOIN table t2 ON (t1.NAME=t2.NAME AND t1.GROUP_ID!=t2.GROUP_ID)

php1999

Groups Table:
id (int) 11
title (varchar) 50

AddressBook Table:
id (int) 11
username (varchar) 50
mobile (int) 16

some times I have the same user in the same or another group
so I want to know who has more than 1 record and where it is
later on I will delete it from my address book.

Sample Data:

1 ------- John -------- 187777744 ( Group# 1 )
2 ------- Dad --------- 184000002 ( Group# 1 )
3 ------- Mark -------- 143333321 ( Group# 1 )
4 ------- Dr.Mark -------- 143333321 ( Group# 2 )
5 ------- Mr.Mark -------- 143333321 ( Group# 3 )

The query should return the last 3 rows for user mark only.

laserlight

hmm... so the mobile number is the "real" primary key of the AddressBook? I think you should consider the three table design and avoid this multiple name problem in the first place.