How to select unlink image and delete items from multiple tables?-CodePudding

I am trying to delete category with items in it.

I have 4 tables :

categories

catId

posts

post_id

images

image_post_id

tags

tag_post_id

My sql query is like this for select and unlink images :

SELECT * FROM categories, posts, images, comments, tags 
WHERE catId = 25 
AND post_catId = catId 
AND image_post_id = post_id 
AND comment_post_id = post_id 
AND tag_cat_id = catId 
AND tag_post_id = post_id

but it doesnt show posts, category has over 100 posts in it.

when I search only in category and posts it displays all.

CodePudding user response：

You are using INNER JOIN, with it you will get results when related records are found in all 4 tables. You need to use LEFT JOIN, similar to this:

SELECT * FROM categories
 LEFT JOIN posts on post_catId = catId
 LEFT JOIN images ON image_post_id = post_id 
 LEFT JOIN comments ON comment_post_id = post_id 
 LEFT JOIN tags ON tag_cat_id = catId AND tag_post_id = post_id
  WHERE catId = 25;

Then you will see all data related to your catid=25.