Inserting into intersection table referencing foreign key

Question

Prop PropID PropDescription

Many to Many relationship

Image ImageID ImageDescription

Image_has_Prop Prop_PropID Image_ImageID

In my application, I need to insert data into the Image_has_Prop . The user types in the ImageId, and then selects the Prop from a JComboBox which displays the PropDescription field. I'm trying to figure out how to relate the description to it's foreign key, is it even possible? Because I suppose the PropDescription may not be unique. Below is my attempt, I think it is way off if Im honest, using mysql database.

INSERT INTO Image_has_Prop 
(Image_ImageID, Prop_PropID)
VALUES
(Select ImageID FRom Image where ImageID ='1', SELECT PropId FROM Prop Where PropDescription = 'Tablets');

David Faber · Accepted Answer · 2012-03-09T13:03:48.733

INSERT INTO Image_has_Prop (Image_ImageID, Prop_PropID)
SELECT 1, PropID
  FROM Prop
 WHERE PropDescription = 'Tablets';

You already know the ImageID you want so no need to query for it. If necessary you could do a subquery (change "1" to "( SELECT ImageID FROM Image WHERE ImageID = 1)"). That would look something like this:

INSERT INTO Image_has_Prop (Image_ImageID, Prop_PropID)
SELECT ( SELECT ImageID FROM Image WHERE ImageID = 1 ), PropID
  FROM Prop
 WHERE PropDescription = 'Tablets';

Or you might do a cross join since you're getting one row from each table:

INSERT INTO Image_has_Prop (Image_ImageID, Prop_PropID)
SELECT i.ImageID, p.PropID
  FROM Image i, Prop p
 WHERE i.ImageID = 1
   AND p.PropDescription = 'Tablets';

Inserting into intersection table referencing foreign key

1 Answers1