[thelist] [SQL] deleting records from associated tables

[rudy]

> the central table is tblTracking, with the
> records to be deleted in tblTrackingDeletions.

hi sean

that's good, and shows the designer was thinking

> Other tables are joined with tblTracking by foreign key.

so, um, do you have relational integrity in your database?

you didn't say which database you're running

> Since I want existing records to be deleted with
> if there aren't coorespoding records in all of the
> associated tables (tblWorkOrder, tblInvoice, tblData),
> I want to use OUTER JOINs, right?

this sounds like a job for ON DELETE RESTRICT

  RI to the Rescue!!
  http://www.evolt.org/article/RI/18/13276/index.html

if you cannot declare RI, you'll probably have to do something like this --

   delete from tblTracking
     where TrackingKey in
       (select TrackingKey
           from tblTrackingDeletions)
       and WorkOrderNum NOT in
       (select WorkOrderNum
            from tblWorkOrder)
       and InvoiceNum NOT in
       (select InvoiceNum
            from tblInvoice)
       and DataKey NOT in
       (select DataKey
            from tblData)

rudy