classic outer join problem
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: classic outer join problem
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
Re: classic outer join problem
Hi Hank, I think your problem is to find all the files under /uploads by a command like : cd /uploads ls -lR | grep .doc files.txt then load the files.txt into a temporary table TEMPtable that you create for this issue (see http://dev.mysql.com/doc/mysql/en/load-data.html), after truncating it. When data is loaded, you can then delete by : delete from Your_table where attachement not in (select attachement from TEMPtable); commit; to rewrite a not in, see http://dev.mysql.com/doc/mysql/en/rewriting-subqueries.html AND look at use of tempfalg at the bottom of the page. Mathias Selon Hank [EMAIL PROTECTED]: 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
