Guys, I posted this on a couple of sites and I'm surprised I haven't received a response because honestly I thought it was a common situation. Then it hit me to come to Sitepoint, so maybe you can help me out? I'm using MSSQL. Thanks.
I'm trying to insert some data into 1(notes) table that has several foreign keys. The problem is that I'm not given the id of the foreign key, but another field from each table. How do I find the PK of another table from another field in those tables and INSERT the pk into the notes table? For example:
(pk) car_id (ai)
(pk) image_id (ai)
(pk) note_id (ai)
I supply the values :
noteid(not really supplied because auto incrmenting),mustang.jpg,Mustang,Stuff about this car,5/3/12
I have tried:
INSERT INTO notes (image_id,car_id, note_text,date_created)
VALUES ( (SELECT img_id FROM images WHERE image_name = 'mustang.jpg'), (SELECT car_id FROM cars WHERE car_name = 'Mustang'), 'Stuff about this car', '5/3/12'))
I always get the "Subqueries are not allowed in this context. Only scalar expressions are allowed."