Delete recored constrain using Inner join for 3 tables in MySql

Question

I am looking for join query. Actually I have main three table.

- Category [cat_id(PK), cat_name]
- Product  [pro_id(PK), cat_id(FK),pro_name]
- Company  [com_id(PK), cat_id(FK),pro_id(FK),...]

Now I have list of Category in list view. So when user click delete selected category should be delete.

Now I can check for single table Whether same cat_id is used by other two tables or not ?

if(check cat_id is used in other tables or not)  
{
alert("you can't delete because category is used by some other table(s).");
}
else
{
alert("record deleted.");
}

Now i can check same thing from single table using NOT INlike,

DELETE FROM CATEGORY WHERE cat_id NOT IN (SELECT DISTINCT cat_id FROM PRODUCT);

This will only delete selected cat_id if it is not available in PRODUCT table.

Even i have try with JOIN QUERYalso as below,

SELECT CATEGORY.cat_id FROM CATEGORY INNER JOIN PRODUCT ON CATEGORY.cat_id =  PRODUCT.cat_id;

But i don't have idea how to use third table.So, I am wondering how can i check for multiple table.

Please help to solve this query.

If you have setup a referential integrity you could simply try. It the DBMS will prevent you from dropping rows which are used. In case of that you want to drop it under all conditions try CASCADE like mentioned by dianuj. — rekire, Sep 20 '13 at 10:03
http://stackoverflow.com/questions/2914936/mysql-foreign-key-constraints-cascade-delete @dianuj is it like this ? & like if i have cat_id=5 and it is used by pro_id=2 then it will delete pro_id=2 as well as cat_id=5 is it so ? — Chintan Khetiya, Sep 20 '13 at 10:12
Yes if you delete cat_id its related products will be deleted that the cascade concept — M Khalid Junaid, Sep 20 '13 at 10:37
@dianuj Thanks for your comments but its should not okay.below answer is perfectly working for me.Thanks again — Chintan Khetiya, Sep 20 '13 at 10:55

score 4 · Answer 1 · edited Sep 20 '13 at 10:42

4

DELETE FROM CATEGORY WHERE cat_id NOT IN 
(SELECT DISTINCT cat_id FROM PRODUCT) AND cat_id NOT IN
(SELECT DISTINCT cat_id FROM COMPANY)

edited Sep 20 '13 at 10:42

Chintan Khetiya

15,962
9
47
85

answered Sep 20 '13 at 10:19

jphase

396
1
11

It looks like above query would delete every row in the category table except for the ones that didn't exist in the other tables. – Chintan Khetiya Sep 20 '13 at 13:28

score 1 · Accepted Answer · answered Sep 20 '13 at 13:13

If you want to do the delete and the check all in one query, you could use

DELETE
FROM category
WHERE category.cat_id = ? 
    AND (SELECT COUNT(*) FROM product WHERE cat_id = ?) = 0 
    AND (SELECT COUNT(*) FROM company WHERE cat_id = ?) = 0

If you just want to check whether or not there are rows matching that key, you could run

SELECT (SELECT COUNT(*) FROM product WHERE cat_id = ?) 
    AND (SELECT COUNT(*) FROM company WHERE cat_id = ?) AS matching_rows

Undoubtedly Accepted. Thanks for answer.Its perfectly working. — Chintan Khetiya, Sep 20 '13 at 13:26

Delete recored constrain using Inner join for 3 tables in MySql

2 Answers2