locked
Parent-Child-Parent Sql RRS feed

  • Question

  • Hi:

    I have two tables.  My main table contains item and quantity.  The second table contains  material_item, item  and quantity. They are joined on item.  The next step is to check if the material_item contains in the item record in the second table.  If it does not, we insert the record into a brand new table.  If it does, we will repeat the last step.  We will check if the material_item is also an item record.   It is parent/child relationship.  But the child can be a parent as well. 

    Table A

    Item  Quantity

    123    1

    234   2

    Table B

    Material Item     Item    Quantity

    bbb                    123            2

    pop                    bbb           1

    wer                   pop            1

    234                  aaa            1

    Item 123  contains material item bbb, pop, wer  (3 records)

    Item 234 (1 record)

    End Result - I will need to insert 4 records into the database. 

    I am trying to explain this not sure if I explain this clearly.   Hopefully, the example will give you more insight. 

    I have been staring at this for quite a while.  I am hoping a fresh  pairs eyes will be able to tell me on what I need to do. 

    Thanks for your assistance.

    kkmick


    Thursday, November 6, 2014 9:39 PM

Answers

  • It seems to me that your example is the opposite of what is stated in the question "If it does not, we insert the record into a brand new table"

    If this is what you want try something like

    Insert into new table ....

    where not exists (select item from materialitem)


    Andy Tauber
    Data Architect
    The Vancouver Clinic
    Website | LinkedIn

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Thursday, November 6, 2014 11:19 PM