1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

SQL Query

Discussion in 'HTML, Graphics & Programming' started by 1ofaKind, 15 Feb 2006.

  1. 1ofaKind


    Joined: 10 Apr 2004

    Posts: 843

    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?

  2. happytechie


    Joined: 18 Oct 2002

    Posts: 5,601

    Location: Surrey

    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.

  3. sloth


    Joined: 20 Apr 2003

    Posts: 947

    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.
  4. 1ofaKind


    Joined: 10 Apr 2004

    Posts: 843

    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 :)
    Last edited: 15 Feb 2006