SQL Query

[1ofaKind]

Hi All,

I'm new to SQL and I've need to know if this is possible in SQL.

I have a system to deal with pricing of a product. The pricing system works as follows.

Step 1 If the customer has a contracted price use this. If no price go to step 2
Step 2 If the customer is part of a group then check the head office for a price. If no price go to step 3
Step 3 Use the standard price list defined for this customer.

So in effect on a customer we had 3 price lists - contract, head office and default.

Can you do an SQL query to be able to bring back the single price to charge or do you have to be a bit sneakier?

Cheers

 

[1ofaKind]

It will be with mySQL

Basically I will be feeding the data from a 3gl application which follows that data structure - I want to maintain the structure/heirarchy if possible so the user has little or no maintenance (i.e. I can auto feed data in and not worry). I have looked into creating a single table with a unique price reference per customer to make the SQL easy. However when I worked out how many records that would potentially be I soon knocked it on the head.

Thanks for the advice so far.

If its not simple I guess I might have to look at MySql5 which has just introduced stored procedures.

Basically what I'm trying to do is a web based ordering (simple). However due to its more specialised nature the run of the mill cart style systems are no good.

I'm not a novice programmer but I've worked on 3gl lower level stuff all my career (17 odd years) so never been exposed (weird I know) to SQL systems.

In for a penny in for a pound eh