0

I have two table category and subcategory I am trying to delete record from this table using mysql query

Query is=

$sql="Delete t1, t2 From category as t1 
       INNER JOIN  subcategory as t2 on t1.c_id = t2.c_id
       WHERE t1.c_id='$del_c_id' ";

This query only delete row from category(t1) whose primary key used into subcategory(t2) table.

Not delete row from category(t1) whose primary key not used in subcategory(t2).

Yogesh Suthar
  • 30,424
  • 18
  • 72
  • 100
swapnil
  • 323
  • 2
  • 6
  • 13
  • @xdazz No, he's only deleting one row from category and all the related rows from subcategory. – Barmar Sep 28 '12 at 07:23

2 Answers2

2

Try CASCADE option in mysql. Subcategory will be deleted automatically if you delete the category. You need to use the InnoDB storage engine to use this feature.

Here is the accepted answer

Community
  • 1
  • 1
iLaYa ツ
  • 3,941
  • 3
  • 32
  • 48
2

You need to use a LEFT JOIN instead of INNER JOIN. By definition LEFT JOIN returns all results from t1, even if they don't have a match in t2.

See this link for more info on join types: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

Gavin Towey
  • 3,132
  • 15
  • 11