Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Posts
    23

    Answered: Master - Detailed table query

    Hi,

    I have a "Products" table with following columns

    ProductId - INT - PK
    Name - Varchar
    Price Decimal

    I have another table "ProductPriceHistory"

    When Product Price is updated, using a trigger, I store old and new prices in that table. This table has following columns

    ProductPriceHistoryId
    Name
    OldPrice
    NewPrice
    UpdateDate

    I can join this table based on the "Name" columns.

    Now Question is, in my result set - I want to get Product.Name, Product.Price and (for getting the last updated value in table "ProductPriceHistory" ) ProductPriceHistory.NewPrice.

    Challenge is that in history table, I have lots of rows for each product based on differet dates. For each product, I need to get the information from the latest row stored for this product.

    Any help on this, thanks.

    SAR

  2. Best Answer
    Posted by VLOOKUP

    "I tried to throw something together.

    SELECT
    A.NAME
    , A.PRICE
    , C.NEWPRICE

    FROM
    PRODUCTS AS A

    INNER JOIN

    (SELECT
    MAX(UPDATEDATE) AS MAX_DATE
    , NAME

    FROM PRODUCTPRICEHISTORY

    GROUP BY
    NAME) AS B ON A.NAME = B.NAME

    INNER JOIN PRODUCTPRICEHISTORY AS C

    ON A.NAME = C.NAME AND B.MAX_DATE = C.UPDATEDATE
    "


  3. #2
    Join Date
    Oct 2007
    Posts
    161
    Provided Answers: 9
    I would suggest not having the price in your product table, as its just duplication. To get the data as you want, you would need a subselect getting the MAX date for that name.

    Dave Nance

  4. #3
    Join Date
    Oct 2014
    Posts
    291
    Provided Answers: 7
    I tried to throw something together.

    SELECT
    A.NAME
    , A.PRICE
    , C.NEWPRICE

    FROM
    PRODUCTS AS A

    INNER JOIN

    (SELECT
    MAX(UPDATEDATE) AS MAX_DATE
    , NAME

    FROM PRODUCTPRICEHISTORY

    GROUP BY
    NAME) AS B ON A.NAME = B.NAME

    INNER JOIN PRODUCTPRICEHISTORY AS C

    ON A.NAME = C.NAME AND B.MAX_DATE = C.UPDATEDATE

  5. #4
    Join Date
    Mar 2004
    Posts
    23
    Thank you very much

    Best Regards,
    SAR

  6. #5
    Join Date
    Oct 2007
    Posts
    161
    Provided Answers: 9
    that works, but you get the max date for every price. May work now while your price table is fairly small, what happens once you have increased the number of products and updated the prices of each thousands of times. That is going to be awfully slow and painful at some point, when you attempt to get the latest price for a particular product.

  7. #6
    Join Date
    Mar 2004
    Posts
    23
    Hmm, yes you are right. Can we use some SQL Windows Function?

  8. #7
    Join Date
    Oct 2007
    Posts
    161
    Provided Answers: 9
    you can get just the max date for the product you are looking up.

  9. #8
    Join Date
    Mar 2004
    Posts
    23
    What about, if I have 2 columns in the table with old and new prices and keep the history in separate table

Tags for this Thread

Posting Permissions

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