I have a table of school class assignments with the following fields -
the first four fields are the primary key:
Term (enum, "Spring", "Fall","Winter")
Attachement (varchar 225)
The "attachment" field is a pointer to a file in the OS of an uploaded
file, in the format like this:
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.