SQL syntax (INSERTS with multiple foreign keys)

If I want to insert a record while looking up a foreign key, I can do that with:

INSERT INTO stores_products (name, product_id)
SELECT 'storeABC',
       id 
  FROM products 
 WHERE name = 'product123';

(where product_id is a foreign key to the products table)

However, I can't quit开发者_StackOverflowe figure out the syntax when I need to look up foreign keys from multiple tables.

For example, I want to do something like:

INSERT INTO stores_products 
  (name, product_id, owner_id)
SELECT 'storeABC', products.id, owners.id 
 FROM products 
WHERE name = 'product123' 
 FROM owners 
WHERE name = 'owner456';

(The table and column names are just an example, I know it doesn't quite make sense from a database design point of view, but my question is with regards to the syntax...). Thanks.

If there's no relation between the PRODUCTS and OWNERS records, use:

INSERT INTO STORE_PRODUCTS
  (name, product_id, owner_id)
SELECT 'storeABC',
       p.id,
       (SELECT o.id
          FROM OWNERS o
         WHERE o.name = 'owner456')
  FROM PRODUCTS p
 WHERE p.name = 'product123'

Otherwise, if there's a relationship between the OWNERS and PRODUCTS table -- you'd specify it with a JOIN:

INSERT INTO STORE_PRODUCTS
  (name, product_id, owner_id)
SELECT 'storeABC',
       p.id,
       o.id           
  FROM PRODUCTS p
  JOIN OWNERS o ON o.relation_to_product_col = p.relation_to_owner_col
 WHERE p.name = 'product123'
   AND o.name = 'owner456'

Reference:

• Visual Explanation of JOINs

If [products.name] and [owners.name] are unique, you can do a cartesian join using keyword cross join. The result will be one row containing the IDs you are looking for.

insert 
  into stores_products(name, product_id, owner_id)
select 'storeABC' as name
       ,products.id
       ,owners.id 
 from products cross join owners
where products.name = 'product123' 
  and owners.name   = 'owner456';

Try to do it like:

updated:

INSERT INTO stores_products 
  (name, product_id, owner_id)
SELECT 'storeABC', 
       products.id, 
       owners.id 
  FROM products, owners 
    WHERE products.name = 'product123' 
AND WHERE owners.name = 'owner456';