1

I have found several answers very close to a solution, but am unable to apply them due to inexperience.

Like:

SQL Remove almost duplicate rows

SQLite3 Remove almost duplicate rows

I will simplify this to just the columns where the issue is, but I need to return the whole row of data.

Columns: 

RecNbr, MPCNbr, ABCNbr     
     1,    123,    123    
     2,  12080,    123  
     3,    456,    456    
     4,    789,    987    
     5,    321,    987
     6,   0053,    <NULL>
     7,   0021,    ''

When I query that data I would like to see rows 2-7, but not 1.

My Data has two rows that represent the same item, one with different data than the other, I need to return the one where the MPCNbr and the ABCNbr do NOT match, but ONLY when I have another row with the same ABCNbr.

I would like this to be a view so I can query it like a table. More records will be added and this will keep reoccurring so that is why I want the view.

Community
  • 1
  • 1
Yourguide
  • 109
  • 9

4 Answers4

1
select * 
    from table 
  where MPCNbr <> ABCNbr 
    and ABCNbr in (select ABCNbr from t1 where MPCNbr <> ABCNbr)
splash58
  • 26,043
  • 3
  • 22
  • 34
1

http://sqlfiddle.com/#!9/a5f3d/1

SELECT t.* 
FROM table1 t
LEFT JOIN
(SELECT ABCNbr,
  SUM(IF(MPCNbr=ABCNbr,1,0)) flag,
  COUNT(*) cnt
FROM table1
GROUP BY ABCNbr) filter
ON t.ABCNbr = filter.ABCNbr
WHERE (cnt>flag AND t.MPCNbr<>t.ABCNbr)
  OR  (cnt=flag AND t.MPCNbr=t.ABCNbr)

UPDATE if you need to get all emptys records as well you can:

http://sqlfiddle.com/#!9/8e79a/1

SELECT t.* 
FROM table1 t
LEFT JOIN
(SELECT ABCNbr,
  SUM(IF(MPCNbr=ABCNbr,1,0)) flag,
  COUNT(*) cnt
FROM table1
GROUP BY ABCNbr) filter
ON t.ABCNbr = filter.ABCNbr
WHERE (cnt>flag AND t.MPCNbr<>t.ABCNbr)
  OR  (cnt=flag AND t.MPCNbr=t.ABCNbr)
  OR IF(COALESCE(TRIM(t.ABCNbr),'')='',1,0)
Alex
  • 16,739
  • 1
  • 28
  • 51
  • This Solution will not let me create a view, I get an error "View's SELECT contains a subquery in the FROM clause". – Yourguide Aug 28 '15 at 18:45
  • This is actually the best answer.... BUT I am having an issue because some of my ABCNbr fields are NULL and some are empty. The NULL ones are being left out of the final result set. Is there a way to evaluate both NULL or empty as empty so the comparisons can run? As new data comes in I am afraid more NULLS will show up again. I have tried IFNULL() inside the select of the left join, but I still get three kinds of records.... NULL , Empty and ones with ABCNbr – Yourguide Sep 01 '15 at 14:39
  • I don't understand the issue. could you explain with sample of data source and resultset based on that data to illustrate what the problem is. – Alex Sep 01 '15 at 14:46
  • This should clear things up: http://sqlfiddle.com/#!9/8a8f3/1 I have NULL values in my ABCNbr field – Yourguide Sep 01 '15 at 15:29
  • No it is still not clear. What is your expectations? do you need both records returned (id=6,7)? or none of them? – Alex Sep 01 '15 at 15:39
  • I need both records returned... Rows 2-7. Just not Row 1. – Yourguide Sep 01 '15 at 15:47
  • so if you have 100 records with `ABCNbr = NULL` we should get all of them? – Alex Sep 01 '15 at 16:09
  • Correct, I need all the records where ABCNbr = NULL or ABCNbr = '' – Yourguide Sep 01 '15 at 16:16
  • Awesome! Thank you so much. It works! Now the only problem I have is that it is SLOOOOOW. My Table has 79,411 records in it and this query is taking FOREVER. Could I help that with my Indexes? – Yourguide Sep 01 '15 at 16:34
  • yes, you should index `ABCNbr` and `MPCNbr` columns. and second question are those `null`s ok or not? would you have same situation in future or not? – Alex Sep 01 '15 at 16:42
  • You are correct.... I can eliminate them now, and set the default to empty instead of NULL and that way I don't get Null values again. THANKS! – Yourguide Sep 01 '15 at 18:09
0

This is a great use for NOT EXISTS:

SELECT *
FROM the_table AS t1
WHERE MPCNbr != ABCNbr
  OR NOT EXISTS (SELECT 1 FROM the_table AS t2 WHERE t1.ABCNbr = t2.ABCNbr AND t2.ABCNbr = t2.MPCNbr)
VoteyDisciple
  • 37,319
  • 5
  • 97
  • 97
0

select * from table_name where ABCNbr not in ( select ABCNbr from table_name where (select count(ABCNbr) from table_name group by ABCNbr) > 1 and ABCNbr=MPCNbr);

j90
  • 15
  • 4