Hi,

I'm a Business Analyst trying to get my head round some SQL.
I'm no coder, but I'm willing to learn.
I'd greatly appreciate any coding gurus out there who are willing to help me out.

Here's my scenario:

3 Tables with hierarchy

MORTGAGE (parent of - one to many)
APPLICATION (parent of - one to many)
CASE_OWNER

So a MORTGAGE can have many APPLICATIONS
APPLICATION can have many CASE_OWNERS

MORTGAGE joins to APPLICATION on MORT_NO,APP_NO
APPLICATION joins to CASE_OWNER on MORT_NO,APP_NO

I need code which does the following:

If I have many applications hanging off a mortgage I want to delete ALL applications (for archiving purposes)

BUT I can't delete ANY application for that mortgage if ANY application has a stage no > 20
OR I can't delete ANY application for that mortgage if ANY application is less than a year old

Kinda:
Do Not Delete Any APPLICATION for this MORTGAGE
Where CASE_OWNER.STAGE_NO > 20
Or APPLICATION.APP_DATE date is within last 12 months

I know that this is going to have a GROUP BY based on on MORTGAGE
but I'm struggling!!

Any help would be great. Ideally, if you could code it, I can then look at the code and see what's going on.