Can anyone recommend an (SQL? ) Algorithm to resolve multiple many-many exclusions

Question

4.00/5 (1 vote)

See more:

Firstly, I'm new on here - so I'm happy to be slapped down if this is a trivial question, has already been answered, or I've posted it in completely the wrong place.

I'm an ex-developer who's got seriously rusty, and I've come across a problem that I'm blocked on. It might be obvious to someone more experienced, but having brainstormed, discussed and searched for it, I'm currently none the wiser.

I'm working on a legacy system with a pretty awful data structure which I've not options to do anything about. The below's a simplification of the problem, but I think is the best way to explain it.

As regards what I'm trying to do :

In the database I have shipment records A - G

I want to delete all the completed shipments. Shipments A - E are completed, but not F or G.

There are also several Invoices in the system.

Rules :

1. If a completed Shipment is part an Invoice that contains other Shipments that aren't completed, it can't be deleted

2. If a completed Shipment is part an Invoice that contains other Shipments that can't be deleted, it can't be deleted

Problem :

- Invoice I1 contains Shipments A, B and C. As those are all Completed, I could delete them all

- Invoice I2 contains Shipments A, F and G. As F & G are not Completed, I can't delete any of them

- BUT, because A cannot be deleted, by Rule 1, because it is part of Invoice I1, neither can B or C

...we'd then need to check any other invoices B & C are part of...

...and so on to infinity...

...and for the life of me I can't think of a simple, quick SQL statement or algorithm to do this other than by brute force ore recursion-until-end

Any alternative suggestions gratefully received!

Apologies also if this is obvious, but I'm just over-thinking it / not seeing the obvious myself..

What I have tried:

I've tried loading all Completed Shipments into a temporary table, and repeatedly running 'Is member of Invoice containing non-Completed Shipments' and 'Is member of Invoice containing un-delete'able Shipments' repeatedly until I get a False result..

..but that doesn't feel hugely efficient...

Posted 5-Jul-16 23:34pm

W Augur

Updated 5-Jul-16 23:59pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2016-07-05T23:59:00

First off, no, this is the right place - and welcome to CP!

SQL has an EXCEPT clause: EXCEPT and INTERSECT (Transact-SQL)[^] which removes entries from one query that are present in the other.
So what I'd do is:
1) SELECT all shipments that are completed.
2) SELECT all shipments that are in invoices with uncompleted items.
3) Use EXCEPT to remove everything in 2 from 1.
4) SELECT invoices which contain only items left in 3.

I don't know how complex this is to do with your system - I don't know the data structure - but that should resolve to three selects:

SQL

SELECT ... FROM
   (SELECT completed
    EXCEPT SELECT uncompleted)

With either a JOIN or a WHERE on the outer one depending on your data structure.
Once I'd got it checked damn carefully, it could become a DELETE.