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 RoosWell, 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