I know this is probably best suited for an sql forum; but there's a lot of bright folks here.
Here is the best analogy of what I need to do:
Given 3 tables:
Archives table (archiveFilename, ArchiveSize, ArchiveDate, ArchiveID)
Files Table (Filename, Filesize, Filedate, FileID)
Links table (FileID, archiveID)
a one to many condition exists: One archive file can contain one or more files. Importantly, multiple archive files can contain the same file.
For instance:
Today.zip contains File1.dat, File2.dat, & File3.dat
Yesterday.zip Contains File2.dat, File4.dat
LastWeek.zip contains File5.dat, file6.dat
What I need to do is remove all archive files that are older than 30 days, provided it leaves 5 (or more) copies of each file .
I do not have access to the actual archive or files -- just hte 3 sql tables -- I need to submit a "remove archive todayzip" type request to another process.
Can anyone think of a slick sql statement to either select all archive files to keep or delete?