[SOLVED] Delete from table where another table has no row match

[fitzsic]

Hi

 

I am trying to create an SQL statement which when executed would delete a table row only if another table did not have a match.

 

So it would be something like this:

 

delete from albums where album.name is not in all tracks.album rows

Table to read/delete is album and table to compare with is tracks.  If the album.name is not found in any tracks table rows (count=0) then delete the album name from the album table as it has no tracks.

 

Any help would really be appreciated.

[JAY6390]

DELETE FROM `albums` WHERE `album`.`name` NOT IN (SELECT `tracks`.`album`)

That will do it ONLY if `tracks`.`album` is the actual album name

 

It would be better if you used id's rather than names since ids are always unique