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 4 of 4
  1. #1
    Regular Coder
    Join Date
    Oct 2010
    Location
    Florence, MS
    Posts
    486
    Thanks
    10
    Thanked 33 Times in 32 Posts

    Calculating sales

    Okay, so I have a table such as this:

    sales:
    id,
    formula_id,
    user_id,
    price

    distributor_prices:
    id,
    distributor_id,
    formula_id
    price


    The idea here is that every time a formula is sold it is added to the sales table. The only issue is that every user is apart of a distributor group, that distributor group may only make a set price off of a certain formula. I want to calculate the sales for store while implementing the distributor_prices where needed if that user_id is in a distributor group.

    Any ideas?
    Notice: If you post a problem and it gets fixed, please remember to go back and place it as solved. ;)
    I always recommend the HEAD First series of books for learning a new coding language. ^_^

  • #2
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,721
    Thanks
    25
    Thanked 662 Times in 661 Posts
    I don't see a correlation between the user_id and the distributor_id. Is there one?
    Evolution - The non-random survival of random variants.

    "If you leave hydrogen alone, for long enough, it begins to think about itself."

  • #3
    Regular Coder
    Join Date
    Oct 2010
    Location
    Florence, MS
    Posts
    486
    Thanks
    10
    Thanked 33 Times in 32 Posts
    I am sorry I totally forgot to include that.

    Yes, there is.

    distributors:
    id,
    parent_id,
    lft,
    rght,
    user_id
    Notice: If you post a problem and it gets fixed, please remember to go back and place it as solved. ;)
    I always recommend the HEAD First series of books for learning a new coding language. ^_^

  • #4
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,721
    Thanks
    25
    Thanked 662 Times in 661 Posts
    I was hoping a DB guru would have appeared and answered this for you. I am not the guy to trust and to make doubly bad, I have no idea what the columns in table distributors mean.

    I'd run this first and see what you get:
    SELECT P.formula_id, P.price
    FROM distributor_prices P, sales S, distributors D
    WHERE P.distributor_id = D.id
    AND D.user_id = S.user_id

    And if it's good then:
    INSERT INTO sales(formula_id , price )
    SELECT P.formula_id, P.price
    FROM distributor_prices P, sales S, distributors D
    WHERE P.distributor_id = D.id
    AND D.user_id = S.user_id

    There are no guarantees with this.
    Evolution - The non-random survival of random variants.

    "If you leave hydrogen alone, for long enough, it begins to think about itself."


  •  

    Posting Permissions

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