Join regardless of match?

[Kryptix]

Say I have two tables...

 

staff

staff_addresses

 

If both tables don't have a match I want to delete the other tables record.

 

DELETE `staff`.*, `staff_address`.* FROM `staff` JOIN `staff_addresses` ON (`staff`.`id` = `staff_addresses`.`staff_id`) WHERE `staff`.`id` IS NULL OR `staff_addresses`.`id` IS NULL;

 

I can use a LEFT or RIGHT join if I know which won't have an entry, but is it possible to do a join anyway regardless if it has a match?

[fenway]

I don't follow.

[Kryptix]

You know how LEFT or RIGHT JOIN will join regardless if there's a match, I want it to join without having to define LEFT or RIGHT if that makes sense...

 

If I'm joining the above, regardless if there's an entry in address OR staff, it joins anyway (displays the result)

[Muddy_Funster]

You could try the following:

DELETE staff.*, staff_address.*
FROM staff, staff_address 
WHERE (staff.id NOT IN (SELECT staff_id FROM staff_address)) 
OR (staff_address.staff_id NOT IN (SELECT id from Staff)

I doubt that will work though, and if it does I'm not sure what it will actualy perform, I have never tried to simultaniuosly delte from two tables whilest using a cross reffence check on just those tables.  I personaly would use two DELETE queries - one for each table - in this case.

[fenway]

You can't have mysql do BOTH a LEFT & RIGHT JOIN at the same time.

[kickstart]

Hi

 

Seems what you need is a FULL OUTER JOIN, which mysql doesn't support.

 

You could possibly emulate it using a LEFT OUTER JOIN and UNIONing the results with a RIGHT OUTER JOIN.

 

However to be honest I would be a bit fearful of doing something like this on a bulk delete.

 

All the best

 

Keith

[fenway]

See here.