#1
  1. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    660
    Rep Power
    31

    Retrieving data from two tables and make it one table?


    Im stuck on how to do this.

    I have two tables A and B.
    A = productCode(PK):namerice
    B = productCode(FK):qty:buyer

    I am trying to create a new table where it becomes
    buyerroductCode:namerice

    That way when I list and access the products a buyer has bought, I can also access the names and price of the product.

    Right now, I can only access table B and get a list of productCodes and quantity the buyer has bought but I also need to display the name of the products the buyer has bought.

    If you have a better approach to what I am doing, please suggest.

    Any help appreciated!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,140
    Rep Power
    4274
    Originally Posted by rePete
    If you have a better approach to what I am doing, please suggest.
    yes

    leave different data in different tables, and combine them when retrieving using a join query

    do some research on data normalization
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    Originally Posted by r937
    yes
    leave different data in different tables, and combine them when retrieving using a join query
    true, but note that prices can change and you'll want to display the price of the product at the time of the purchase so it's common practise to store a copy of the price in the purchase table, like a receipt.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2004
    Posts
    2,971
    Rep Power
    375
    another way i have seen is this:

    products ( product_id, name, .......)
    order_items ( order_id, product_id,..)
    order (order_id, user_id, date_ordered)

    Product_prices ( product_id, price, start_date, end_date)

    then you update this table every time price changes AND you reference this when you want to calculate the prices. This way you can get all historical prices

IMN logo majestic logo threadwatch logo seochat tools logo