0

I have columns in a table product named id and name. Each row is a product with a single product code.

I would like to retrieve the names and IDs of similar-sounding products with different codes. Anyone know what the basic structure of such a query should look like in mySQL?

user2781658
  • 19
  • 2

2 Answers2

0

Your last where clause should be a HAVING clause, not a where clause. You are using an aggregate function on a row-level filter.

See SQL - having VS where

Community
  • 1
  • 1
Rob Conklin
  • 8,806
  • 1
  • 19
  • 23
0

the following query gives you the name of the products repeated 2 or more times:

select name, count(*) from product group by name having count(*) > 1;

the following gives you the name of the products:

slect name from (select name, count(*) from product group by name having count(*) > 1) as many_codes;

and finally the following should give you the result you are looking for:

select name, id from product where name in (slect name from (select name, count(*) from product group by name having count(*) > 1) as many_codes);
SLeptons
  • 17
  • 3