Thread: how to INSERT foreign keys?

I have an <items> table, and a <keys> table, and a map table that relates
item_id's to key_id's. How do I populate item_keys with data?

I tried:

INSERT INTO item_keys SELECT item_id from items where item = 'item 1',
SELECT key_id from keys where key = 'key 1';

but the parser doesn't like that.

Thanks!

Phil,

> I have an <items> table, and a <keys> table, and a map table that
>  relates item_id's to key_id's. How do I populate item_keys with
>  data?

> INSERT INTO item_keys SELECT item_id from items where item = 'item
>  1', SELECT key_id from keys where key = 'key 1';

Hmmm ... a shortcut?  OK:

INSERT INTO item_keys (item_id, key_id)
SELECT item_id, key_id
FROM items, keys
--OR:  FROM items CROSS JOIN keys
WHERE item = 'item 1' AND key='key 1';

If you're absolutely sure that there is only one Key 1 and only one
 Item 1 in the table.

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco