Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003
    Location
    Hoofddorp - The Netherlands
    Posts
    9

    Exclamation Unanswered: Discount-calculation

    Hello everybody,

    I'm busy with a website with a MySQL-database, and I want to build in a discount-calculation. Customers who log in at the have their own general discount, but there can be an exception for a few products.
    I have the following tables and fields (only the ones I use in the script):

    - discount
    - discount_id
    - product_id
    - company_id
    - productdiscount

    - companies
    - company_id
    - companydiscount

    - users
    - user_id
    - company_id

    - products
    - product_id
    - name
    - price

    The first thing that has to be calculated is the companydiscount.
    And for the products that have a alternative discount, I of course want to show that alternatvie discount.

    I wrote the following SQL-script:

    select products.product_id, products.name, products.price, discount.discount_id, discount.product_id, discount.company_id, discount.productdiscount, companies.company_id, companies.companiediscount, users.user_id, users.company_id,

    (price)-(price/100*productdiscount) as alternative_discount,
    (price)-(price/100*companydiscount) as general_discount

    from products, companies, users, discount

    where companies.company_id = users.company_id and
    products.product_id = discount.producte_id


    The only thing that happens now is that only the product that have an anlternative discount are showed. How can I make a script that show all the products with the general discount, but the products with the alternative discount with the right calculated discount?

    I hope anyone can help me!

    Many thanks,

    Michael Roos

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, you should be posting this in the MySQL forum, but I can see right off that you haven't linked your Companies and Discounts tables. You are essentially running a crossjoin query, and I don't think that is your intention.

    blindman

Posting Permissions

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