MySQL Delete Row does not delete values ​​in many tables if necessary

Question

I am introducing my own blogging system to better understand the object-oriented side of PHP, mySQL and jQuery AJAX. I have a many-to-many relationship between Post_T and Tag_T, including the associative PostTag_T object. When creating a message, I added functions that will check if this tag already exists, as well as add a new tag and match, or just add a match to an existing tag.

The failure I noticed is that when deleting Mail, the tag string in Tag_T still exists while the mapping in PostTag_T is deleted (set to ON DELETE CASCADE). How can I remove this tag if it is not used by any other mail? I have a message id when deleting a message.

Useful logic
Using the message identifier from the deletion, delete from tag_t, where in posttag_t pID = post id AND no other messages (pID) in posttag_t use the same tag identifier .

delete from tag_t where tID = (select tID from posttag_t where pID='post id') AND.. 

For your convenience, here is a visual representation.

Created post line in Post_T:

Display message and tag in PostTag_T:

Tag line created in Tag_T:

On removal: using AJAX POST with data calling the php file in the url (assuming I might need to add a query that looks for tables ..)

Answer 1

When you delete a message due to ON DELETE CASCADE , you no longer have rows in the posttag_t table, so it is futile to try to match it. This means that you have left only the option to remove all tags that are not associated with any messages.

You can do this with a query

 DELETE t FROM tag_t t WHERE NOT EXISTS ( SELECT * FROM posttag_t WHERE tid = t.tid ); 

Here is the SQLFiddle demo

---

Now you can let the trigger take care of this.

 CREATE TRIGGER gt_ad_posttag_t AFTER DELETE ON post_t FOR EACH ROW DELETE t FROM tag_t t WHERE NOT EXISTS ( SELECT * FROM posttag_t WHERE tid = t.tid ); 

Here is the SQLFiddle demo

Answer 2

 DELETE FROM `tag_t` WHERE `tID` IN (SELECT `tID` FROM `posttag_t` WHERE `pID` = 'post_id') AND `tID` NOT IN (SELECT `tID` FROM `posttag_t` WHERE `pID` != 'post_id') 

The last part of the above query check is a message using this tag or not.