how can i delete a record in sql using more than 1 table

Question

I'm having a problem in sql, can anyone help me I have 3 tables sublocation, postingdetail and employee

I have to delete sublocid from sublocation table but first i have to check that is there any employee working on that sublocation from postingdetail table, if yes then the record shouldn't be deleted.

table sublocation

sublocid
sublocname
mainlocid

table postingdetail

empid
subloc
mainloc

table employee

empid
empname
sublocid
mainlocid

Answer 1

Something like

DELETE 
FROM [SUBLOCATION] 
LEFT JOIN [EMPLOYEE] ON [SUBLOCATION].[sublocid] = [EMPLOYEE].[sublocid] 
LEFT JOIN [POSTINGDETAIL] ON [POSTINGDETAIL].[empid] = [EMPLOYEE].[empid] 
WHERE empid IS NULL

That might work.

Answer 2

Well I am not familiar with sql-server, I am a MySQL user myself, but I know sql-server does have stored procedures... and that's what stored procedures were made to do...

Here is a little tutorial on stored procedures.

Answer 3

I don't understand the meaning of the postingdetail table, and why we are taking the sublocid from table employee rather than postingdetail, but you query should look something like this:

delete sublocation
where sublocid not in
(
    select e.sublocid
    from employee e
    join postingdetail p on e.empid = p.emid
)