Conditional SQL Statements?

elskan · #1 (**permalink**) 05-03-07, 05:11

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

aschk · #2 (**permalink**) 05-03-07, 05:35

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"

elskan · #3 (**permalink**) 05-03-07, 06:23

thanks aschk.

aschk · #4 (**permalink**) 05-03-07, 06:43

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.

elskan · #5 (**permalink**) 05-03-07, 07:08

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

aschk · #6 (**permalink**) 05-03-07, 07:34

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

elskan · #7 (**permalink**) 05-03-07, 08:47

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

aschk · #8 (**permalink**) 05-03-07, 09:36

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

?

elskan · #9 (**permalink**) 05-05-07, 05:19

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?

aschk · #10 (**permalink**) 05-08-07, 07:15

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).

blindman · #11 (**permalink**) 05-08-07, 07:49

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'

stolze · #12 (**permalink**) 05-08-07, 11:10

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 ...

blindman · #13 (**permalink**) 05-08-07, 12:08

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.

elskan · #14 (**permalink**) 05-08-07, 14:53

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.

blindman · #15 (**permalink**) 05-08-07, 15:13

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech