List:General Discussion« Previous MessageNext Message »
From:SGreen Date:May 24 2005 9:08pm
Subject:Re: classic outer join problem
View as plain text  
Hank <heskin@stripped> 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

Thread
data length vs index length ??Michael Gale24 May
  • Re: data length vs index length ??mfatene24 May
  • Re: data length vs index length ??Dan Nelson24 May
  • Re: data length vs index length ??SGreen24 May
  • classic outer join problemHank24 May
    • Re: classic outer join problemSGreen24 May
    • Re: classic outer join problemmfatene25 May