Tricky Deleting statements

[iLifeNetworks]

I have been working at this for some time. I am attempting to delete records on multiple tables within same database where the vendor_id matches the vendor ID for a given state ID on one table.

 

For example.

Tables:

vendors (main table where we match vendor_id field from)

vendors_to_category (contains vendor_id)

vendors_service_areas (contains_vendor_id)

 

So, how do we delete any records from the vendors_to_category table as well as vendors_service_areas table if the vendor_id doesn't exist in the vendors table?

 

i have tried some different inner joins, left joins, cross joins, with no success...

 

delete from vendors.*, vendors_photo_gallery.*, vendors_service_areas.*, vendors_to_category.* 
USING vendors v
INNER JOIN vendors INNER JOIN vendors_photo_gallery INNER JOIN vendors_service_areasINNER JOIN vendors_to_category
where v.vendor_id = vendors_photo_gallery.vendor_id
or
v.vendor_id = vendors_service_areas.vendor_id
or
v.vendor_id = vendors_to_category.vendor_id

[Ken2k7]

Why not run 3 simple delete queries?

[andrewgauger]

 

delete from vendors_to_category where vendor_id NOT IN(SELECT vendor_id from vendors);

 

This might scope to ...vendors_to_category, vendors_service_area where... but I don't have a practical way to test it.  Go ahead and try it out.

[Mchl]

I think you want something like this:

DELETE
  vendors.*, vendors_photo_gallery.*, vendors_service_areas.*, vendors_to_category.*
FROM 
  vendors v
CROSS  JOIN 
  vendors_photo_gallery 
USING(vendor_id)
CROSS JOIN 
  vendors_service_areas 
USING(vendor_id) 
CROSS JOIN 
  vendors_to_category 
USING(vendor_id)