0

I have a table that has duplicate data. A field in the table has some typo's so I am trying to find all the rows in this table where the other column does not have the same information.

For example

partnumber

warehouse   int_pn    ext_pn
=========   ======    ======
1           ABC100    XYZ001
2           ABC100    XYZ001
1           ABC200    XYZ021
2           ABC200    XYY021
3           ABC999    XYZ999

In the table above, int_pn ABC200 exists in two warehouses (1 and 2) but in ext_pn for warehouse 2 there is a typo

I am trying to list all the rows where int_pn appears more than once but have a different ext_pn

http://sqlfiddle.com/#!6/96248d/1

The result of a query should return

result

warehouse   int_pn    ext_pn
=========   ======    ======
1           ABC200    XYZ021
2           ABC200    XYY021

I am having a hard time building a SQL query to do this

Thanks

Ian
  • 137
  • 1
  • 11
  • Possible duplicate of [Finding duplicate values in a SQL table](https://stackoverflow.com/questions/2594829/finding-duplicate-values-in-a-sql-table) – Valerica Jan 16 '18 at 10:48

2 Answers2

0

You can achieve this by self-join

SELECT p1.warehouse
    ,p1.int_pn
    ,p1.ext_pn
FROM partnumber AS p1
INNER JOIN partnumber AS p2 ON p1.int_pn = p2.int_pn
    AND p1.ext_pn != p2.ext_pn

Demo: http://sqlfiddle.com/#!6/96248d/28

Mittal Patel
  • 2,732
  • 14
  • 23
  • Thank you - I was heading in this direction but could not work it out – Ian Jan 16 '18 at 11:08
  • if you have more than 2 lines with he same int_pn, rows will be multiplicated. You can avoid this with the `distinct` keyword – Kobi Jan 16 '18 at 11:15
0

You can group data by int_pn and then count the distinct values of ext_pn

http://sqlfiddle.com/#!6/96248d/29

select warehouse, int_pn, ext_pn
from partnumber 
where int_pn in (
   select int_pn 
from partnumber
group by int_pn
having count(distinct ext_pn) >1)
Kobi
  • 2,494
  • 15
  • 30