[SQL] probleem met duplicaten verwijder SQL

[zoepercavia]

Misschien een leuk probleem voor de vroege ochtend (of late nacht). Ik ben een bestelsysteem aan het maken. Alle producten hebben een uniek productId. Elk trimester kan er opnieuw besteld worden, er mogen echter geen 2 dezelfde producten per persoon besteld worden (daar gaat dit over). Als een product echter verkocht is, kan hetzelfde product wel weer besteld worden.
Een voorbeeld van aantal bestellingen staat in het plaatje.

Om duplicaten verwijderd te worden heb ik een mooie SQL geschreven. Deze zou alle duplicate bestellingen moeten verwijderen, tenzij een bestelling al verkocht is.
In onderstaande tabel moeten dus, na uitvoering van de SQL, id's 188, 189, 190, 191 en 192 verwijderd worden. Dan zijn alle duplicaten verwijderd, en er wordt rekening gehouden met het feit dat id=98 al verkocht is.

Het probleem: ook id=183 wordt verwijderd. Blijkbaar gaat de detectie van verkochten niet goed, terwijl ik dat toch opgenomen heb (verkocht=false). Ik heb hier al een hele tijd naar lopen kijken, maar ik zie et niet (en googlen heeft ook weinig zin). Iemand die me kan helpen?

code:

SQL = _
"DELETE FROM TblBestellingen" & _
" WHERE TblBestellingen.Id IN (" & _
    "SELECT Duplicaten.Id FROM TblBestellingen AS Duplicaten" & _
        " WHERE EXISTS (" & _
            "SELECT RelProductId, RelLidId, count(id) FROM TblBestellingen" & _
                " WHERE TblBestellingen.RelProductId = Duplicaten.RelProductId" &_
                " AND TblBestellingen.RelLidId = Duplicaten.RelLidId" & _
                " AND verkocht=false" & _
            " GROUP BY TblBestellingen.RelLidId, TblBestellingen.RelProductId" & _ 
            " HAVING COUNT(TblBestellingen.Id)>1" & _
        ")" & _
    ")" & _
" AND TblBestellingen.Id NOT IN (" & _
    "SELECT Min(id) FROM TblBestellingen AS Duplicaten" & _
        " WHERE EXISTS (" & _
            "SELECT RelProductId, RelLidId, count(id) FROM TblBestellingen" & _
                " WHERE TblBestellingen.RelProductId = Duplicaten.RelProductId" &_
                " AND TblBestellingen.RelLidId = Duplicaten.RelLidId" & _
                " AND verkocht=false" & _
            " GROUP BY TblBestellingen.RelLidId, TblBestellingen.RelProductId" & _ 
            " HAVING COUNT(TblBestellingen.Id)>1" & _
        ")" & _
        " GROUP BY RelProductId, RelLidId" & _
    ")" & _
""

[EfBe]

zoepercavia schreef op 16 december 2002 @ 00:50:
Elk trimester kan er opnieuw besteld worden, er mogen echter geen 2 dezelfde producten per persoon besteld worden (daar gaat dit over). Als een product echter verkocht is, kan hetzelfde product wel weer besteld worden.

Dat laatste komt semantisch neer op meerdere keren hetzelfde verkopen aan dezelfde persoon. Ik begrijp niet waarom je dan eerst het ene niet toestaat maar via een vage regel later weer wel. (M.a.w.: bij het beter formuleren van je eigen regels voorkom je dat je systeem niet goed functioneert omdat je regels/functionaliteit uberhaupt niet klopt).

Nu de code, ik heb even de VB crap eromheen weggehaald, die is nl. niet nuttig voor het verhaal, zodat het wat overzichtelijker wordt. Ik heb code waarvan ik denk dat het ook dubbelen verwijderd in jouw tabel onder jouw code gezet.

Dit is jouw query:

code:

DELETE  FROM TblBestellingen
WHERE   TblBestellingen.Id IN (
    SELECT  Duplicaten.Id 
    FROM    TblBestellingen AS Duplicaten
    WHERE   EXISTS (
        SELECT  RelProductId, RelLidId, count(id) 
        FROM    TblBestellingen
        WHERE   TblBestellingen.RelProductId = Duplicaten.RelProductId
            AND TblBestellingen.RelLidId = Duplicaten.RelLidId
            AND verkocht=false
        GROUP BY TblBestellingen.RelLidId, TblBestellingen.RelProductId
        HAVING COUNT(TblBestellingen.Id)>1
        )
    )
    AND TblBestellingen.Id NOT IN (
        SELECT  Min(id) FROM TblBestellingen AS Duplicaten
        WHERE   EXISTS (
            SELECT  RelProductId, RelLidId, count(id) 
            FROM    TblBestellingen
            WHERE   TblBestellingen.RelProductId = Duplicaten.RelProductId
                AND TblBestellingen.RelLidId = Duplicaten.RelLidId
                AND verkocht=false
            GROUP BY TblBestellingen.RelLidId, TblBestellingen.RelProductId
            HAVING COUNT(TblBestellingen.Id)>1
            )
        GROUP BY RelProductId, RelLidId
        )

Ik denk dat dit hetzelfde doet:

code:

DELETE  FROM TblBestellingen
WHERE   id NOT IN
    (
        SELECT  TOP 1 id
        FROM    tblBestellingen as T1
        WHERE   TblBestellingen.RelProductID=T1.RelProductID
            AND verkocht=false
        ORDER   BY id ASC
    )

Niet live getest natuurlijk en aangezien ik je functionaliteit dubieus vindt, begrijp ik niet helemaal wanneer een product wel dubbel mag zijn en wanneer niet, ik heb verkocht=false toegevoegd, maar dat kan wellicht fout zijn. Het idee is dat je tijdens het doorlopen van de TblBestellingen, een rijtje rows opvraagt die moeten blijven met dezelfde eigenschappen. Dit rijtje is in jouw geval 1 rij, nl. van de dubbele degene met het laagste id, vandaar die top1.

[ Voor 3% gewijzigd door EfBe op 16-12-2002 08:52 ]

[zoepercavia]

Bedankt voor je hulp. Ik ben het wel met je eens dat verkocht een beetje vaag is, maar zo'n slecht oplossing is het niet. Waar het op neer komt is dat ik alle duplicaten wil verwijderen, waarbij de verkochten uitgesloten zijn.

Wat betreft jouw query, die werkt evenmin, omdat er niet per persoon en per product, maar alleen per product gekeken wordt.

[Mart!]

Kijk eerst eens welke ID's je terugkrijgt bij de twee sub-selects en post deze hier eens. Dan kunnen we eerder constateren waar de fout optreedt. De tip is dus: breek je probleem op in sub-probleempjes die doorzichtiger zijn.

[Goodielover]

Kan jij de query ook even in normale taal verwoorden.
Zoiets als,
verwijder alle bestellingen waarvoor geldt dat er een andere bestellingen is die .....

Ik heb het vermoeden dat je de twee subqueries kan integreren door in de eerste een vergelijking te maken met het ID uit de hoofd-query. Dan verwijder je dus alleen die records waarvoor geldt dat er een ander record is met kleiner ID.

Ik heb bovendien het vermoeden dat je een aantal condities ook al in de hoofd-WHERE kan plaatsen. Er mogen toch alleen records verwijderd worden met verkocht=false. Zet dit dan ook in die where clause.
Snap je?

[EfBe]

zoepercavia schreef op 16 December 2002 @ 10:29:
Bedankt voor je hulp. Ik ben het wel met je eens dat verkocht een beetje vaag is, maar zo'n slecht oplossing is het niet. Waar het op neer komt is dat ik alle duplicaten wil verwijderen, waarbij de verkochten uitgesloten zijn.

Wat ik bedoelde was: als je eerst duplicaten uitsluit om reden ABC, dan kun je die later niet toestaan. Immers: je datamodel is dan onderheving aan semantische interpretatie van regels die niet in de database te realiseren zijn.

Wat betreft jouw query, die werkt evenmin, omdat er niet per persoon en per product, maar alleen per product gekeken wordt.

Oh, dat is waar inderdaad. Maar je kunt de subquery van mijn query uitbreiden met die tests. Je begrijpt de logica achter mijn oplossing? (Indien ja, dan moet je hem kunnen aanpassen aan jouw situatie).