Results 1 to 9 of 9
  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
    Oct 2002
    Location
    Plymouth UK
    Posts
    116
    You need a slight modification to your where statement:

    where companies.company_id = users.company_id and
    products.product_id(+) = discount.product_id

  3. #3
    Join Date
    Jul 2003
    Location
    Hoofddorp - The Netherlands
    Posts
    9

    Modification doesn't work

    Hi gannet,

    Thanks for your reply!

    The script:
    "where companies.company_id = users.company_id and
    products.product_id(+) = discount.product_id"
    doesn't work. I get a error :

    "Invalid SQL Query Error: [TCX][MyODBC]You have an error in your SQL syntax near '(+) = discount.product_id'"

    I think a script like this is the solution for the problem, but maybe it has to be a changed a little bit.

    Michael

  4. #4
    Join Date
    Oct 2002
    Location
    Plymouth UK
    Posts
    116
    Many apologies I didn't notice it was mysql, the command I gave was for Oracle. You need a left join.

    Try modifying the product clause with

    LEFT JOIN products ON
    products.product_id = discount.product_id

  5. #5
    Join Date
    Jul 2003
    Location
    Hoofddorp - The Netherlands
    Posts
    9
    Hello Gannet,

    Thanks for your advice, I have the script working except one thing.
    The script now is:
    -------------------------------------------------------------------
    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 users, products INNER JOIN discount ON products.product_id = discrount.product_id RIGHT JOIN companies ON companies.company_id = discount.company_id

    WHERE companies.company_id = users.company_id
    -------------------------------------------------------------------

    Now it shows the discounts in the right way but the only problem is: it only shows the product_id when the product has an alternative discount. And I need the product_id to be shown at every product, no matter what discount it has.

    Thanks in advance,
    Michael

  6. #6
    Join Date
    Oct 2002
    Location
    Plymouth UK
    Posts
    116
    The INNER JOIN between the products and discount tables will only return records where the product_id is present in both tables.

    I think what you want are the records for all products regardless of a record in the discount table. In which which case the join is not an INNER but a LEFT JOIN. Try modifying the join on product_id

  7. #7
    Join Date
    Jul 2003
    Location
    Hoofddorp - The Netherlands
    Posts
    9
    I did both INNER JOIN and LEFT JOIN but no differtents in this case

  8. #8
    Join Date
    Oct 2002
    Location
    Plymouth UK
    Posts
    116
    In your SQL statement the first join is on the discount table, I think this should the products table.

  9. #9
    Join Date
    Jul 2003
    Location
    Hoofddorp - The Netherlands
    Posts
    9

    Thumbs up It's working

    hi!

    Good news it's working. The joins where good but there was an other problem and I fixt it. In the SELECT statement there where fields I don't had to select.

    Not good:
    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,

    Good:
    SELECT products.product_id, products.name, products.price, discount.productdiscount, companies.companiediscount, users.user_id

    Thanks for helping with the JOIN statements!

    Michael

Posting Permissions

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