from the sample database , How could I make a query where I could search all the region ( region id) where Kivuto Id is duplicated?

Question

enter image description here

I need to fetch the 3 lines as highlighted in the result with green i.e separate region id but same kivuto id.I need to rectify such products so that I could correct the kivuto id's

You are expected to try to **write the code yourself**. After [doing more research](https://meta.stackoverflow.com/q/261592/1011527) if you have a problem **post what you've tried** with a **clear explanation of what isn't working** and provide [a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve). Read [How to Ask a good question](http://stackoverflow.com/help/how-to-ask). Be sure to [take the tour](http://stackoverflow.com/tour) and read [this](https://meta.stackoverflow.com/q/347937/1011527). — Sloan Thrasher, Aug 13 '18 at 08:21

score 0 · Answer 1 · edited Aug 13 '18 at 08:47

0

Try this.

select * from table_name 
  where kivuto_id in (
    select email from table_name
      group by kivuto_id
      having count(*) > 1
  )

You can refer to this as well: Find rows that have the same value on a column in MySQL

edited Aug 13 '18 at 08:47

Nico Haase

11,420
35
43
69

answered Aug 13 '18 at 08:41

Ruchira

115
1
2
8

score 0 · Answer 2 · answered Aug 13 '18 at 10:35

You can simply use exists:

select t.*
from t
where exists (select 1
              from t t2
              where t2.kivuto_id = t.kivuto_id and
                    t2.region_id <> t.region_id
             );

For performance, you want an index on (kivuto_id, region_id).

from the sample database , How could I make a query where I could search all the region ( region id) where Kivuto Id is duplicated?

2 Answers2