SQL - 根据另一个表中的行数删除行

Question

How would I go about deleting a row from the table 'subjects' that has a primary id 'subject_id' based on the number of rows in another table named 'replies' that uses a 'subject_id' column as a reference.

Example in pseudo code: If ('subject' has less than 1 reply){ delete 'subject'}

I don't know much about SQL triggers so I have no clue if I would be able to incorporate this directly in the database or if I'd have to write some PHP code to handle this...

Answer 1

To delete any subjects that have had no replies, this query should do the trick:

DELETE s.* FROM subjects AS s
WHERE NOT EXISTS 
    (
    SELECT r.subject_id
    FROM replies AS r
    WHERE r.subject_id = s.subject_id
    );

Demo: DB Fiddle Example