SQL Query

1ofaKind · 15 Feb 2006 at 18:45

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

happytechie · 15 Feb 2006 at 18:48

what databse system is it? it might be esier to do in a procedural bersion (PL/SQL for Oracle or T-SQL for SQL Server)

you can do what you want with a single query but the exact syntax will deplen on your database.

HT

sloth · 15 Feb 2006 at 18:58

Depending on the database, I'd create a function or a stored procedure to do this (1 place to update if your re-use it across apps/views/queries/stored procs). If you tell us which Database this is, with simplified version of the relevant tables, someone could bash something together.

1ofaKind · 15 Feb 2006 at 19:57

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