title: Delete both children and parent SQL records
body: <p>I wish to delete a child of a given id, and also delete the parent if the child's parent no longer has any more children after the child is deleted. CASCADE DELETE works great for deleting the children of a deleted parent, however, this is not my intent.  Foreign key constraints are enforced, and I do not wish to temporarily disable.  I would rather not use a trigger or stored procedure.</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS parents (
    id INT NOT NULL,
    data VARCHAR(45) NULL,
    PRIMARY KEY (id))
  ENGINE = InnoDB;
  
  CREATE TABLE IF NOT EXISTS children (
    id INT NOT NULL,
    data VARCHAR(45) NULL,
    parentsId INT NOT NULL,
    PRIMARY KEY (id),
    INDEX fk_children_parents_idx (parentsId ASC),
    CONSTRAINT fk_children_parents
      FOREIGN KEY (parentsId)
      REFERENCES parents (id)
      ON DELETE CASCADE
      ON UPDATE NO ACTION)
  ENGINE = InnoDB;
  </code></pre>
  
  <p>Sample Data</p>
  
  <pre><code>parents
  id data
  1  foo1
  2  foo2
  
  children
  
  id parentId data
  1         1 bar1
  2         2 bar3
  3         2 bar3
  </code></pre>
  
  <p>For instance, with the above data, deleting child #1 should delete parent #1, however, deleting child #2 or (not and) #3 should not delete parent #2.</p>
  
  <p>I can do it in three queries (1. get parentsId, 2. delete child, 3. delete parent if it doesn't have any children), however, feel it could/should be better.</p>
  
  <p>I was thinking something similar to the following, however, it is not correct.</p>
  
  <pre><code>DELETE c, p FROM children c
  LEFT OUTER JOIN parents p ON p.id=c.parentsId AND c.id!=123
  WHERE c.id=123 AND p.id IS NULL;
  </code></pre>
  
  <p>How can this be performed in one or a maximum of two queries?</p>