SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict
    Join Date
    May 2001
    Location
    USA
    Posts
    244
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Inner Join for a parent/child relationship?

    Hi all-
    I have an application with a parent/child relationship, both records are in the same table. I'm trying to write a MSSQL script to run nightly to make an update to the cost of the child. So my table is something like this (simple form):

    ID| UPC | DESCRIPTION | COST | PRICE | PARENT_ID | PARENT QTY
    1 | 123 | 6PK BEER | 6.00 | 8.00 | <NULL> | <NULL>
    2 | 345 | SINGLE BEER | 1.00 | 2.00 | 1 | 6

    My application is setup to update the cost of the master pack (the 6pk in this case) but the cost update doesn't roll down to the child. The formula would be Cost = 6/6, but I'm struggling with how to write the script.

    I was thinking an inner join but can't seem to formulate the syntax. Any ideas?

    Thanks much.

  2. #2
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    Yorkshire, UK
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Assuming that your table is called 'tblStock', this should do it;

    Code SQL:
    UPDATE A
         SET A.COST = B.COST/A.PARENT_QTY
         FROM tblStock AS A
                 INNER JOIN tblStock AS B ON A.PARENT_ID = B.ID
    Last edited by FatBat; Mar 25, 2011 at 07:58. Reason: formatting
    There's more to me than meets the eye, but not much more.

  3. #3
    SitePoint Addict
    Join Date
    May 2001
    Location
    USA
    Posts
    244
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Works perfectly. Thanks much!

  4. #4
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    Yorkshire, UK
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I guess you should include a check to make sure you're not trying to divide by zero, just to be on the safe side.
    There's more to me than meets the eye, but not much more.

  5. #5
    SitePoint Addict
    Join Date
    May 2001
    Location
    USA
    Posts
    244
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    good call.


Bookmarks

Posting Permissions

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