another sql-question

[wu_trax]

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?

[Raging.Retard]

Off the top of my head I would try something like ...

SELECT PID, producttable.CID, some_more_data, 
   AcutalDiscount = CASE productable.discount
      WHEN producttable.Discount > categorytable.Discount THEN categorytable.Discount
      ELSE producttable.Discount
   END
FROM producttable INNER JOIN categorytable ON categorytable.CID = producttable.CID

I just made that up. I've not tested it in the slightest. There may even be a more elegant way to do it given some more time, but it Saturday night. Time to go.

[wu_trax]

you are my new god.
it works !!!, even with mysql. and i didn't even know such things are possible within sql. (instead of ActualDiscount = ... i had to put AS actualDiscount at the end, but still, it works !!!

thanks a lot