Inserting Missing Rows

Question

I've got an interesting SQL puzzle I haven't been able to solve, hopefully one of you have more SQL-Fu than I do. I have two tables set up something like this:

tblFormElement
    FrmElementID
    Label

tblFormSubmissionElement
    FrmID_fk
    FrmElementID_fk
    SubmitID_fk
    SubmissionValue

And if I run the following SQL:

SELECT tblFormElement.Label, tblFormSubmissionElement.* FROM tblFormElement
JOIN tblFormSubmissionElement ON FrmElementID = FrmElementID_fk
WHERE FrmID_fk = 55
AND SubmitID_fk = 7062

I'll get a result set that looks like this:

Label   | FrmID_fk | FrmElementID_fk | SubmitID_fk | SubmissionValue

Label1  | 55       | 1601            | 7062        | Foo1
Label2  | 55       | 1602            | 7062        | Foo2
Label3  | 55       | 1603            | 7062        | Foo3
Label4  | 55       | 1604            | 7062        | Foo4
Label5  | 55       | 1605            | 7062        | Foo5

Now if I change the SubmitID_fk to anything below 7062, I'll get a result like this:

Label   | FrmID_fk  | FrmElementID_fk | SubmitID_fk | SubmissionValue

Label1  | 55        | 1601            | 5011        | Foo1
Label2  | 55        | 1602            | 5011        | Foo2
Label3  | 55        | 1603            | 5011        | Foo3
Label5  | 55        | 1605            | 5011        | Foo5

What I would like to do is execute an INSERT statement for every one of these 
result sets which lack that Label4 row. Or in other words, For each result set 
which does not contain a row with FrmElementID_fk equal to 1604, INSERT the 
row with the respective columns. Any thoughts on how to attack this problem?

Answer 1

Using your select query:

 Insert into OtherTable (
 SELECT tblFormElement.Label, tblFormSubmissionElement.* 
 FROM tblFormElement
      JOIN tblFormSubmissionElement 
      ON FrmElementID = FrmElementID_fk
 WHERE FrmID_fk = 55
 AND SubmitID_fk <> 1604)

Is that what you were looking for?

Answer 2

I think you want something like this.

INSERT INTO tblFormSubmissionElement
    SELECT 55, 1604, SubmitID_fk, 'Foo4'
    FROM tblFormSubmissionElement
    WHERE SubmitID_fk < 7026
        AND FrmElementID_fk = 1603;

There's probably a way to use DISTINCT instead of matching the FrmElementID_fk to 1603, but I think this should work too.