ok, so i build this online-shop system which supports a pretty much flexible database-structure. before something is added to the shopping cart a specified select statement is execuded (to recive to product-data).
now i need to add some kind of discount-system that allows the customer (as in the guy who bought our shop, not the guy who buys things in that shop) to specify a discount rate. my idea was to look in the result of that select-statement if there is a field called discount. if yes use whatever is in there. this works fine as long as there is only one place to specify the discount rate.
examples:
- customer a says id like to specify a discount rate for each of my product.
in that case id just add another field to the product table, do a SELECT * FROM producttable WHERE pid = whatever, no problem
- customer b says id like to specify a discount rate for each of my customers, so i just do a select * from producttable, customertable where pid = whatever and customerid = id of logged in customer
- customer c wants a discount for each category so i just join the category table with the producttable.
this works great. but now comes customer d who wants to specify a discount rate for each category, but for some products he wants higher / lower discount rate. this leaves me with a problem because now i have two columns called discount and i need to select to lower or higher one.
to keep it simple and because my explanations are usually crap the table structure would look something like that:
categorytable:
- cid (primary key)
- some_more_data
- ...
- discount
producttable:
- pid
- some_more_data
- ...
-discount
- category_cid
what i need is a single select statement that joins those two tables, gives me all that data in the product-table (except for the discount field) and the lower of those two discount rates as discount. database is an old and stable mysql-database, so now subselects or other usefull things
as i asaid, the db-structure doesn't matter, so add / change whatever you want, the only condition is that i have only one select.
is that even possible?