Delete when no other rows are using a relationship?

Question

I have a users table and an images table.

A user has only one image.

An image can belong to many users.

I'm trying to make it so that when a user is deleted, it's image is deleted but only if no other users are using that image.

I've tried to use on delete cascade but it only works the opposite way to what I'm trying to achieve - when you delete an image, the user is deleted.

Users:
id | name | image_id

Images:
id | url

Answer 1

You can find the not used image with

  select  i.id, i.url  
  from  images i 
  left join  users u  on u.image_id = i.id
  where  image_id is null 

and delete

delete  i.* 
from  images i  
inner join  (

      select  i.id
      from  images i 
      left join  users u  on u.image_id = i.id
      where  image_id is null 
) t on t.id  = i.id 

if you have issue related to the fact that you are delete row that are involved in select then

delete  i.* 
from  images i  
inner join  ( 
  select  id 
  from ( 

      select  i.id
      from  images i 
      left join  users u  on u.image_id = i.id
      where  image_id is null  
  ) t1 
) t on t.id  = i.id