Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Apr 2005
    Posts
    30

    Unanswered: Conditional SQL Statements?

    Hi There

    I am writing a shopping cart page and I need some help in 'doing the math' for an SQL statement.

    I have a main cart table that adds all the products.prices together from the 'price' field in the database to give the total amount payable.

    Code:
    SELECT SUM(TotPric) AS TheTotal FROM  ( SELECT cart.cart_quantity * product_options.price AS TotPric FROM product_options INNER JOIN (products INNER JOIN (cart INNER JOIN main ON cart.main_id = main.main_id) ON products.product_id = main.product_id) ON product_options.product_options_id = main.product_options_id WHERE cart.session_id = "&cookiesesh&")AS TTT;"
    I have however introduced a 'sale_price' that I would like the statement to select over the 'price' if the sale_price isn't 0.

    Do I need to have another select statement in the mix to specifically select the sale_price if not zero, something like

    Code:
    SELECT SUM(TotPric) AS TheTotal FROM  ( SELECT cart.cart_quantity * (SELECT product_options.price AS sp  WHERE po.sale_price <> 0) AS TotPric FROM product_options INNER JOIN (products INNER JOIN (cart INNER JOIN main ON cart.main_id = main.main_id) ON products.product_id = main.product_id) ON product_options.product_options_id = main.product_options_id WHERE cart.session_id = "&cookiesesh&")AS TTT;"
    I am a bit confused. Can you point me in the right direction, maybe a link to a tutorial or something.

    I appreciate any help.

    Cheers

  2. #2
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Rewritten for viewing

    Code:
    SELECT SUM(TotPric) AS TheTotal,
        FROM  (
            SELECT (c.cart_quantity * po.price) AS TotPric
            FROM product_options po 
            INNER JOIN main m ON po.product_options_id = m.product_options_id
            INNER JOIN products p ON p.product_id = m.product_id
            INNER JOIN cart c ON c.main_id=m.main_id
            WHERE c.session_id = "&cookiesesh&"
        ) AS TTT"
    Potential answer

    Code:
    SELECT SUM(TotPric) AS TheTotal,
        FROM  (
            SELECT (c.cart_quantity * IF(po.sale_price>0,sale_price,price) AS TotPric
            FROM product_options po 
            INNER JOIN main m ON po.product_options_id = m.product_options_id
            INNER JOIN products p ON p.product_id = m.product_id
            INNER JOIN cart c ON c.main_id=m.main_id
            WHERE c.session_id = "&cookiesesh&"
        ) AS TTT"
    Last edited by aschk; 05-03-07 at 07:00.

  3. #3
    Join Date
    Apr 2005
    Posts
    30
    thanks aschk.

  4. #4
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Does it work? What DBMS are you using? Can you provide a sample table layout? Do you have some sample data for a test? If you could provide this information I'm sure I could give you a better answer.

  5. #5
    Join Date
    Apr 2005
    Posts
    30
    Hi Aschk

    Its not working. Thanks for the effort though.

    I am using MySQL.

    Tables

    tblCart
    cart_id,session_id,main_id,cart_quantity,timestamp

    tblProduct_Options
    product_options_id, product_options_text,price,sale_price,stock

    tblMain
    main_id,product_id,product_options_id

    tblProducts
    product_id,product_name,description

    (some fields removed for clarity)

    I am basically trying to do is mutliply cart_quantity with the sale_price if the sale_price is not zero, if it is zero then multiply by the price.

    I have included the sql_dump with all the data.

    Thanks for your time and effort
    Attached Files Attached Files

  6. #6
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Code:
    SELECT SUM(TotPric) as TheTotal
        FROM  (
            SELECT (c.cart_quantity * IF(po.sale_price>0,sale_price,price)) AS TotPric
            FROM product_options po
            JOIN main m ON po.product_options_id = m.product_options_id
            JOIN products p ON p.product_id = m.product_id
            JOIN cart c ON c.main_id=m.main_id
            WHERE c.session_id = '@sessionid'
        ) ttt

  7. #7
    Join Date
    Apr 2005
    Posts
    30
    The original answer worked. Sorry aschk, I can't even copy and paste. time to hang up my coding hat and apply for that Burger King vacancy.

    Thanks for your help and your time


  8. #8
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    i was just looking through your table structure and had a question regarding a few columns you are using.

    In your products table you are using a `text` column for your product name. I was wondering why? I suspect your names of products won't be larger than 255 variable characters so use a VARCHAR(255).

    Also the same for your product_code and cart_finish columns in your cart and main tables. I can't see a reason why these need to be text columns either.

    Maybe a rethink on those or explanation would be good ?

  9. #9
    Join Date
    Apr 2005
    Posts
    30
    Thanks for pointing this out to me.

    Definately a rethink.

    I have recently moved from access to mysql and haven't really studied the benefits of choosing the right field type or best practises for mysql.

    I am looking into it now.

    Do you have any advice or know of any sites I can look at before I google it myself.

    I am guessing that VARCHAR keeps the database size down & makes it quicker?

  10. #10
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    In a word yes. I'm not 100% keyed up on the data types especially with regards to Text, however what I do is that it's a self altering column meaning that data could continually be placed in it and it could differ per row of the database. Unless you're dealing with text of an unknown size it's best to restrict fields to sensible values. e.g. usernames will probably be no longer than 30 characters and thus you column shouldn't need to be anything bigger than varchar(30).

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You know, using a subquery here is superfluous. This will return the same result with less obfuscation:
    Code:
    SELECT Sum((c.cart_quantity * IF(po.sale_price>0,sale_price,price)) AS TheTotal
    FROM product_options po
    JOIN main m ON po.product_options_id = m.product_options_id
    JOIN products p ON p.product_id = m.product_id
    JOIN cart c ON c.main_id=m.main_id
    WHERE c.session_id = '@sessionid'
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The IF thing is not SQL. A case expression has to be used:
    Code:
    SELECT SUM(c.cart_quantity * CASE po.sale_price > 0 THEN sale_price ELSE price END) AS theTotal
    FROM ...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by stolze
    The IF thing is not SQL.
    The poster does not indicate what platform he is using, but he did say that the code worked as written. You are correct that the statement is not in the correct syntax for MSSQL (or Oracle, I think), but it may execute for Access which is bastardized with VB.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Apr 2005
    Posts
    30
    First of all: thanks Blindman and Stolze for the time you took in posting to this thread.

    My Db is MySQL and I am using classic ASP to write the 'other bits'

    I am a newb to this so I am a little lost when it comes to 'The IF thing is not SQL'

    What I am trying to do is write fast, correct, standard code and I appreciate your input.

    As Blindman pointed out the original query supplied by Aschk works fine, what I would like to know is: Are there performance gains to be made by losing the subquery, is it more 'standard and correct' to use, as Stolze said, the CASE expression instead.

    I can see myself coming across this situation again and again and it would be good for me to get it right inthe future.

    Thanks again for your input guys. enlightening and educating.


  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    There are certainly no performance GAINS to be had by using unnecessary subqueries. If you are a consultant being paid to code by the line, then that method may work out well for you, but otherwise it just makes it more difficult to debug.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •