SQL Filtering duplicates by different columns in query

Question

on this thread i had a question which was successfully answered.

searching for filtered duplicates in access query

Now i have a second problem. Maybe someone can help me.

I have a query which looks like this:

query A_Gravur:

SP1 | SP2     | SP3

 64 | CB75000 | 3
 64 | empty   | D
 70 | CB70010 | 1
 70 | CB70010 | 1
119 | CB70050 | 5
119 | empty   | D
165 | S15002  | Schalter
165 | S15002  | Schalter
194 | R70034  | Poti
194 | R10023  | Potentiometer
196 | S10063  | Schalter
196 | S10063  | Schalter
197 | S10063  | Schalter
198 | S10070  | 3
199 | CB75000 | 5

This query is filtered by another query to check for duplicates. The final result looks like this:

Result:

SP1 | SP2     | SP3

194 | R70034  | Poti 470k
194 | R10023  | Potentiometer
196 | S10063  | Schalter
197 | S10063  | Schalter
 64 | CB75000 | 3
199 | CB75000 | 5

and the SQL Code for this looks like this:

  SELECT SP1, SP2, SP3
  FROM A_Gravur
  WHERE (SP1 IN
    (SELECT SP1
      FROM A_Gravur
      WHERE SP3<>'D'
      GROUP BY SP1
      HAVING COUNT(*)>1)
  OR SP2 IN
    (SELECT SP2
      FROM A_Gravur
      WHERE SP3<>'D'
      GROUP BY SP2
      HAVING COUNT(*)>1))
  AND CONCAT(CAST(SP1 AS CHAR),SP2,SP3) NOT IN
    (SELECT CONCAT(CAST(SP1 AS CHAR),SP2,SP3)
      FROM A_Gravur
      WHERE SP3 <> 'D'
      GROUP BY CONCAT(CAST(SP1 AS CHAR),SP2,SP3)
      HAVING COUNT(*)>1)
  AND SP3<>'D'
  ORDER BY SP1, SP2;

SO NOW MY NEW PROBLEM:

I added 2 new columns to the query A_Gravur (SP4 and SP5) and did the duplicate search

SP1 | SP2     | SP3           | SP4     | SP5       |

194 | R70034  | Poti 470k     |123546   |88859     
194 | R10023  | Potentiometer |88859    |
196 | S10063  | Schalter      |7773     |
197 | S10063  | Schalter      |99992    |
 64 | CB75000 | 3             |177777   |
199 | CB75000 | 5             |99999    |

Explain: in SP4 there are always values (Materialnumber). Now when i'm searching for duplicates it should only find this result:

SP1 | SP2     | SP3           | SP4     | SP5       |

196 | S10063  | Schalter      |7773     |
197 | S10063  | Schalter      |99992    |
 64 | CB75000 | 3             |177777   |
199 | CB75000 | 5             |99999    |

because the Number in SP5 (88859) is also in SP4 (88859) and SP1 is in both records the same number (194)

but if it looks like this then the result is correct

SP1 | SP2     | SP3           | SP4     | SP5       |

194 | R70034  | Poti 470k     |123546   |0000001     
194 | R10023  | Potentiometer |88859    |
196 | S10063  | Schalter      |7773     |
197 | S10063  | Schalter      |99992    |
 64 | CB75000 | 3             |177777   |
199 | CB75000 | 5             |99999    |

Thank you for your help!!

I have just seen your question and I don't understand one thing. Do you ignore the value in SP5 or must you compare it with the value in SP4. — grahamj42, Mar 24 '13 at 12:34
I have to compare it with sp4 - but only if sp1 is the same in both rows. Sp5 is the preferred materialnumber which should be used in my programm when sp1 has duplicates — user2153413, Mar 24 '13 at 13:45
I am still a little unclear. Where does **0000001** come from in your last table? — grahamj42, Mar 24 '13 at 13:55
My list is generated by SAP system. All values in sp4 come from there. I have to complete the sp5 column. First i search for duplicates in sp1. After that i search for duplicates in SP2. After that all duplicates with an letter D in SP3 will be removed. After that all duplicates which are the same in sp1 and sp2 will be removed. And finally when the value in sp5 is anywhere in sp4 (compare) than it should be also removed from the final result - so forget the value 0000001 it was just an example — user2153413, Mar 24 '13 at 14:07
Your English is fine, but your problem is a little complex ! — grahamj42, Mar 24 '13 at 14:12
It is a complex compare system for helicopters. When there are special equipments in the helicopter with a value in sp5 than the equipment with the value in sp5 is preffered and it is no mistake when duplicates are found. So both duplicates should disappear in the final result — user2153413, Mar 24 '13 at 14:23
I think I understand your explanation starting *My list is generated by SAP system*, but for me there's a problem with your example data : for SP1 = 194 you have two different values for SP2, so for me 194 is still not in the result set. — grahamj42, Mar 24 '13 at 19:03

score 0 · Accepted Answer · answered Mar 24 '13 at 19:14

0

This might do what you want:

SELECT SP1, SP2, SP3, SP4, SP5
FROM A_Gravur
WHERE (SP1 IN
  (SELECT SP1
    FROM A_Gravur
    WHERE SP3<>'D'
    GROUP BY SP1
    HAVING COUNT(*)>1)
OR SP2 IN
  (SELECT SP2
    FROM A_Gravur
    WHERE SP3<>'D'
    GROUP BY SP2
    HAVING COUNT(*)>1))
AND CONCAT(CAST(SP1 AS CHAR), SP2,
    CAST(COALESCE(SP5, SP4) AS CHAR)) NOT IN
  (SELECT CONCAT(CAST(SP1 AS CHAR), SP2,
    CAST(COALESCE(SP5, SP4) AS CHAR))
    FROM A_Gravur
    WHERE SP3 <> 'D'
    GROUP BY CONCAT(CAST(SP1 AS CHAR),SP2,
      CAST(COALESCE(SP5, SP4) AS CHAR))
    HAVING COUNT(*)>1)
AND SP3<>'D'
ORDER BY SP1, SP2, SP3, SP4;

CONCAT() chooses the first field which is not null, so will choose SP5 if it's present, otherwise SP4.

Here is the SQL Fiddle. If I haven't understood correctly, please would you amend the test data there and add a comment with the address of the new SQL Fiddle so I have good data to work on.

answered Mar 24 '13 at 19:14

grahamj42

2,752
3
25
34

Hi there, i just tested the result - it looks very good- i will tomorrow test it at my job and then i will give you my final answer- much thanks till now, maybe you can explain me how you work that i also will be so good as you :-) – user2153413 Mar 24 '13 at 21:02
If it works OK, let me know and I'll be happy to edit the answer to give some explanation for the day when you add some other fields ;-) One little explanation in cas you need it: I assumed SP4 and SP5 were integers and used `CAST(... AS CHAR)`. If these fields are character types, you don't need the cast. – grahamj42 Mar 24 '13 at 21:06
Hi very good! I needed a little bit to convert the coalesce function to ms access but now it is working very good!! Much thanks. Now i can continue with my vba codes. – user2153413 Mar 25 '13 at 17:24

SQL Filtering duplicates by different columns in query

1 Answers1