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