MySQL: Simple Query to Find Duplicates?

[msmith29063]

I've looked online but haven't been able to find what I'm looking for. I just need a simple MySQL query to display all the records from a database if one of their fields (products_model) is being used by more than one record.

Something like...

ID products_name products_model
===========================
12 Product A 1345
237 Product G 1345
83 Product L 211
901 Product X 211
68 Product S 211

Any help you can provide would be great. Thank you for your time!

[Charles]

Something like:select *
from TABLE_NAME
where products_model in (
select products_model
from (
select products_model, count(*) as "Num"
from TABLE_NAME
group by products_model
)
where "Num" > 1
)

[msmith29063]

I think this is close. I get an error message that says:
Every derived table must have its own alias

[Charles]

That error message would suggest trying select *
from TABLE_NAME
where products_model in (
select products_model
from (
select products_model, count(*) as "Num"
from TABLE_NAME
group by products_model
) s
where "Num" > 1
)But I've just noticed a note here that "MySQL does not support such subqueries."

[chazzy]

any reason to not use a HAVING clause?

[Charles]

any reason to not use a HAVING clause?That'll get rid of one level of nesting and perhaps that will be enough.select *
from TABLE_NAME
where products_model in (
select products_model
from TABLE_NAME
group by products_model
having count (*) > 1
)

[msmith29063]

For the first one, it doesn't return anything. And it should. There are duplicates.

For the second one, we're getting a syntax error.

Any ideas? Thanks again for your help!

[Phill Pafford]

Try something like this


SELECT *, COUNT(products_model) AS dup_rec
FROM table_name
GROUP BY products_model HAVING COUNT(products_model) > 1
ORDER BY id ASC