Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    TNO
    TNO is offline
    Regular Coder
    Join Date
    Apr 2005
    Posts
    213
    Thanks
    2
    Thanked 1 Time in 1 Post

    Inserting Missing Rows

    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:

    Code:
    tblFormElement
        FrmElementID
        Label
    Code:
    tblFormSubmissionElement
        FrmID_fk
        FrmElementID_fk
        SubmitID_fk
        SubmissionValue
    And if I run the following SQL:

    Code:
    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:

    Code:
    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:

    Code:
    
    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 SubmitID_fk which lack that Label4 Row. Or in other words:
    For each result set which does not contain a row with FrmElementID_fk row equal to 1604, INSERT the row with the respective columns.

  • #2
    TNO
    TNO is offline
    Regular Coder
    Join Date
    Apr 2005
    Posts
    213
    Thanks
    2
    Thanked 1 Time in 1 Post


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •