Insert into with multiple selects

Question

I have a table in which I want to insert data, and the values itself have to come from multiple tables. This has to be done as follows, by reading the MySQL documentation:

insert into flight(airlinecompanyId,planetypeId)
select id from airlinecompany where naam = 'Brussels Airlines',
select id from planeType where type = 'Boeing 737';

So simply explained, I want to insert the id's from the tables airlinecompany and planetype that I demand via the where clausule, into the flight table column.

When I try this query, I keep getting the following error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' select id from planeType where type = 'Boeing 737'' at line 2

Someone who has a solution?

Answer 1

Having no relationship between airlinecompany and planeType, you needn't necessarily perform a JOIN to do the insert, but since you are creating only a single row in flight, it can easily be done with subselects, wrapping each table's SELECT statement in ()

INSERT INTO flight (airlinecompanyId, planetypeId)
  SELECT
    (SELECT id FROM airlinecompany WHERE naam = 'Brussels Airlines'),
    (SELECT id FROM planeType WHERE type = 'Boeing 737')
  /* MySQL will permit this with no FROM clause */

It can alternatively be done with a CROSS JOIN, since there's only one possible row returned:

INSERT INTO flight (airlinecompanyId, planetypeId)
  SELECT
    ac.id,
    pt.id
  FROM
    airlinecompany ac
    CROSS JOIN planeType pt
  WHERE
    ac.naam = 'Brussels Airlines'
    AND pt.type = 'Boeing 737'