title: Mysql: insert a new row in a child table with new values except for foreign key column which is obtained from parent table
body: <p>I'm having this situation. i want to insert a new row into a table which has a foreign key constraint to another table. when i insert a new row, i have new values obtained from user input except for the foreign key column which is to be obtained from the parent table. and the foreign key is an auto_increment primary key in the parent table. here is a the minimized schema to illustrate, only the relevant columns are shown.</p>
  
  <pre><code>CREATE TABLE CUSTOMER (
    CUSTOMER_CODE INTEGER NOT NULL AUTO_INCREMENT,
    FIRST_NAME VARCHAR (20) NOT NULL,
    EMAIL VARCHAR (50) UNIQUE NOT NULL,
        .
    .
    .
  
  
    PRIMARY KEY (CUSTOMER_CODE)
  );
  
  CREATE TABLE BOOKING (  
    BOOKING_CODE INTEGER NOT NULL AUTO_INCREMENT,
    CUSTOMER_CODE INTEGER NOT NULL,
    BOOKING_DATE DATE NOT NULL,
    .
    .
    .
  
    PRIMARY KEY (RESERVATION_CODE),
    CONSTRAINT BOOKING_CUSTOMER_FK FOREIGN KEY (CUSTOMER_CODE) REFERENCES CUSTOMER   
                                                                          (CUSTOMER_CODE)
  );
  </code></pre>
  
  <p>i want to know the insert statement that will feed into the BOOKING table a new row with values generated based on user interaction with the system for the rest of the columns (like <code>booking_date</code>) but obtain the <code>customer_code</code> from the customer table with a <code>where email=</code> clause with an email obtained from user input.  </p>