How to query a database for empty tables

Question

In my system there are two tables called ‘faculty’ and ‘faculty_assets’.

I need to delete faculty by faculty from the ‘faculty’ table. But system should allow deleting the faculty when only faculty_assets table empty. Otherwise It should not allows to delete the faculties from ‘faculty’ table.

Can anyone modify below sql query? (‘fac_id’ is the primary key of ‘faculty’ table )

function removeFaculty($fac_id){
    $conn=new connection();
    $sql="delete from faculty where fac_id='$fac_id'";
    $result=$conn->query($sql);
    return $result;
}

Gordon Linoff · Accepted Answer · 2014-08-09 14:47:14Z

2

You can do this with a not exists clause:

delete from faculty
    where fac_id = '$fac_id' and
          not exists (select 1 from faculty_assets);

answered Aug 9, 2014 at 14:47

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jens · Accepted Answer · 2014-08-09 14:50:03Z

0

Try to do so:

DELETE t1 FROM faculty t1 LEFT JOIN faculty_assets t2 ON t1.fac_id=t2.fac_id  WHERE t2.fac_id IS NULL;

answered Aug 9, 2014 at 14:50

Jens

69.6k15 gold badges107 silver badges133 bronze badges

Collectives™ on Stack Overflow

How to query a database for empty tables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related