Hank <[EMAIL PROTECTED]> wrote on 05/24/2005 05:02:47 PM: > I have a table of school class assignments with the following fields - > the first four fields are the primary key: > > Year (int) > Term (enum, "Spring", "Fall","Winter") > ClassID (int) > SectionID (int) > Attachement (varchar 225) > > The "attachment" field is a pointer to a file in the OS of an uploaded > file, in the format like this: > "/uploads/2003/Fall/330/1/conversions.doc" > > When an old class item is imported into a new class item for a new > year/term/class/section (new record), the "attachment" field is copied > over, and the OS file stays where it is.. the attachment field value > is then a pointer the to the OS file for a previous year/term (i.e. > the OS file is not copied to the new location filesystem structure). > > I've been trying to construct a self-joining query to list all the > attachments which are NOT referenced by some future > year/term/class/section. The desired query result is a list of files > I can DELETE - i.e. files not imported or being pointed to by any > other class_item record in a different year/term. > > Keep in mind that pointer to files in the same Year/Term (but > different class/section) are NOT to be deleted. > > The system currently has MySQL version 4.0.1, so I can't use > subqueries (i.e. NOT IN (...)). > > Any suggestions would be greatly appreciated. thanks. > -Hank > > -- It's almost trivial to detect what is NOT in a list, if you have a list to compare to. Do you have a table listing all of the files in the "catalog" area on your disk? If you do then we can detect which files are no longer in use and those will be the ones you can delete.
Something else you may be able to do is to run a query returning the MAX(year) for each attachment SELECT attachment, max(year) FROM catalogtable GROUP BY attachment; Then eliminate all of the attachments that are too old. Just some ideas. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
