table1
refno status
1     A
2     A
6     A
3     A

table2
refno itemcode qty
1     1        5
1     2        0
3     8        0
3     1        0
2     4        3
6     7        0

table1
refno status
1     A
2     A

table2
refno itemcode qty
1     1        5
2     4        3

DELETE t1.*, t2.*
FROM Table t1
LEFT JOIN
(
   SELECT *
   FROM Table2
   WHERE qty = 0
) t2 ON t1.refno = t2.refno
WHERE t2.refno IS NULL

delete  t1, t2
from table1 t1
left join table2 t2 on t1.refno = t2.refno
where t2.qty = 0 or t2.refno is null;

create procedure CleanTable1Table2()
begin
  delete from table2 t2
  where qty = 0;

  delete from table1 t1
  where not exists (select null from table2 t2
                    where t2.refno= t1.refno);
end