Query to look for duplicates entries in a rows

Hello

I have a mysql database, in which one of the columns is called "Aliases".

I need a query that I can run in phpmyadmin which will search for any exact duplicates of those aliases and present them to me so I can edit them.

For example if there are two aliases named  mickey-mouse    then that could be considered duplicates and I want to just see those duplicates in the results of the query.  if there was also a mickey-mouse-club then that would not be an exact match and I don't want to see that included in the mickey-mouse match results.

Editing them is no problem. I just want to easy find those duplicates so I can make the appropriate fix by hand.

I'm using phpmyadmin Version information: 4.2.10,

I have a backup of my database.  all aliases are lower case and have no spaces -- just-dashes-between-words.

I have about 1000 records and just a few of them have duplicate aliases -- so a query would help me take care of this task.

I've used phpmyadmin for years, but am not a database pro like you people are.

The search is all within one table, which we can call bananas_content  ....

Thanks

Rowby
duplicate-alias.jpg
LVL 9
Rowby GorenAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GaryCommented:
SELECT * FROM tablename
INNER JOIN (SELECT * FROM tablename
GROUP BY column_with_duplicate_values_name HAVING COUNT(id) > 1) dupes
ON dupes.id= tablename.id

Open in new window

0
Rowby GorenAuthor Commented:
Thanks Gary

Ran it but it apparently is looking for the database name too

MySQL said: Documentation

#1046 - No database selected

So I guess I need to include the database name in the query???
0
GaryCommented:
In PHPMyAdmin select your db on the left then run the sql
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Rowby GorenAuthor Commented:
Still getting an error

Just want to make sure I am replacing the placeholders in your example with the correct item:

SELECT * FROM com_content
INNER JOIN (SELECT * FROM com_content
GROUP BY column_with_duplicate_values_name HAVING COUNT(id) > 1) dupes
ON dupes.id= tablename.id

Note that I am using com_content in both places  I think I have to use "alias" in one of the lines....
0
GaryCommented:
column_with_duplicate_values_name

Should be the name of the column containing the duplicate values.
0
Rowby GorenAuthor Commented:
Getting closer

See screen captureerror
0
PortletPaulEE Topic AdvisorCommented:
don't use "select *" in the group by subquery, e.g.

SELECT * FROM com_content
INNER JOIN (
                        SELECT
                                                column(s)_with_duplicate_values_name(s)
                                              , MIN(id)  as ID
                        FROM com_content
                        GROUP BY
                                                column(s)_with_duplicate_values_name(s)
                        HAVING COUNT(id) > 1
                    ) dupes
ON dupes.id= tablename.id

Use MIN(id) or MAX(id) but you need one of these, and group by the column or columns that you consider form a unique record, don't include any more columns than these in the select clause unless they are within an aggregate function such as min() or max()
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GaryCommented:
tablename should be com_content

And
Good point by Paul...
0
Rowby GorenAuthor Commented:
Oops.
0
PortletPaulEE Topic AdvisorCommented:
SELECT * FROM m???_content
INNER JOIN (
                        SELECT
                                                column(s)_with_duplicate_values_name(s)
                                              , MIN(id)  as ID
                        FROM m???_content
                        GROUP BY
                                                column(s)_with_duplicate_values_name(s)
                        HAVING COUNT(id) > 1
                    ) dupes
ON dupes.id= m???_content.id
0
Rowby GorenAuthor Commented:
Not quite there

SELECT * FROM com_content
INNER JOIN (SELECT * FROM com_content
GROUP BY alias HAVING COUNT(id) > 1) dupes
ON dupes.id= tablename.id

Result

mzsah_content is the table name
0
GaryCommented:
ON dupes.id= tablename.id

com_content

and fix the sql per Paul's recommendation
0
GaryCommented:
SELECT * FROM com_content
INNER JOIN (
	SELECT
		alias, MIN(id) as ID
	FROM com_content
	GROUP BY
		alias 
	HAVING COUNT(id) > 1
) dupes
ON dupes.id= com_content.id

Open in new window

0
Rowby GorenAuthor Commented:
Thanks

SELECT * FROM mxxxx_content
INNER JOIN (
                        SELECT
                                                alias
                                              , MIN(id)  as ID
                        FROM mxxxx_content
                        GROUP BY
                                                alias
                        HAVING COUNT(id) > 1
                    ) dupes
ON dupes.id= mxxxx_content.id

Worked!

I'll award points later.

Thanks!!!
0
Rowby GorenAuthor Commented:
Thanks and sorry for delay in  awarding the points.

Rowby
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.