title: INSERTing values into a child table referenced from parent table
body: <p>I was up all last night trying to crack this but with no luck so I'm hoping you guys can help as I'm all out of ideas:</p>
  
  <p>I have two parent tables that I want to populate a Junction table from:</p>
  
  <p><strong>Brides:</strong></p>
  
  <pre><code>create table if not exists `Brides` ( 
      `BrideID` INT not null auto_increment,
      `MaidenName` varchar(10) unique,
      primary key (`BrideID`)
     ) engine=InnoDB;
  
      insert into Brides (MaidenName)
      values ('Smith'),
             ('Jones')
      ;
  </code></pre>
  
  <p><strong>Churches:</strong></p>
  
  <pre><code>create table if not exists `Churches` (
  `ChurchID` INT not null auto_increment,
  `ChurchName` varchar(10) unique,
  primary key (`ChurchID`)
  ) engine=InnoDB;
  
  insert into Churches (ChurchName)
  values ('St Marys'),
         ('St Albans')
  ;
  </code></pre>
  
  <hr>
  
  <p>I am trying to populate the ID variables for Junction table <strong>Marriages</strong> by indirectly referencing the unique names in each parent table. In addition, I'm looking to include <code>MarriedName</code> to identify if a Bride marries more than once:</p>
  
  <p><strong>Marriages:</strong></p>
  
  <pre><code>create table if not exists 'Marriages' (
  'BrideID' INT not null,
  'ChurchID' INT not null,
  'MarriedName' TEXT not null
  primary key ('BrideID','ChurchID','MarriedName')
    INDEX `fk_Marriages_Brides1_idx` (`BrideID` ASC),
    INDEX `fk_Marriages_Churches1_idx` (`ChurchID` ASC),
    CONSTRAINT `fk_Marriages_Brides1`
      FOREIGN KEY (`BrideID`)
      REFERENCES `Brides` (`BrideID`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
    CONSTRAINT `fk_Marriages_Churches1`
      FOREIGN KEY (`Church_ID`)
      REFERENCES `Churches` (`ChurchID`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION)
  ;   
  </code></pre>
  
  <p>I'm trying to do something like the below pseudo-code (although I'm pretty sure it wouldn't be the smart way to do it anyway as must be slow with with so many sub-queries):</p>
  
  <pre><code>insert into Marriages (Bride_ID, Church_ID, MarriedName)
    select b.BrideID, c.ChurchID, m.MarriedName
    from (values (Bride,Church,MarriedName)
           ('Smith','St Marys','Johnson'),
           ('Jones','St Albans','Peterson')
           ) m
  
    join Brides b
    on a.MaidenName=m.Bride
    join Churches c
    on m.Church=c.ChurchName;
  </code></pre>
  
  <p>Any help/insight/corrections you have would be greatly appreciated!</p>