Deleting from multiple tables

query issues in php

[femiot]

Course table
cid(course id)
cname

course_student table
sno(student id)
cid(course id)

Student table
sno(student id)
name

Hello peeps! I need your help on this one... the query i need is when i delete the record from course table it should delete all the records where that cid = sno in course_student(without leaving orphans) and where sno of course_student = sno of student at once! (php5 and mysql)

Thanks

[femiot]

$result = " SELECT * FROM course, course_student, student WHERE course.cid = course_student.cid AND course_student.sno = student.sno AND course.cid = '$cid'";
$result = "DELETE course,course_student,student FROM course LEFT JOIN course_student ON course.cid = course_student.cid LEFT JOIN student ON course_student.sno = student.sno WHERE course.cid = '$cid'";

Sorted it out myself cheers!

[coopster]

Good for you femiot! Glad you got it sorted. Now you can dig into a neat little database feature called "triggers" ... always more to learn :)