#1
  1. intellectually challenged
    Devshed Novice (500 - 999 posts)

    Join Date
    Oct 2004
    Location
    m-w.com
    Posts
    664
    Rep Power
    408

    Unexpected Query Results


    I've put together a query expecting it to return all products in a shopping cart that qualify for a discount, depending on the coupon code entered. Trying to handle most of the checking for validity on the DB side, rather than the programming side. On the programming side of things, it's looping through with a WHILE loop. However, I fully expected to see a complete list of the qualifying items when using the query direct. Not sure what I'm missing, but perhaps someone could shed some light on it for me. Help is appreciated.

    SQL Code:
    SELECT  coupons.id AS id, 
                 coupons.code AS code, 
                 coupons.method AS method, 
                 coupons.percentage AS percentage, 
                 coupons.amount AS amount, 
                 coupons.minimum AS minimum, 
                 coupons.uses AS uses, 
                 coupons.used AS used, 
                 coupons.category AS coupon_category,
                 categories.parentid AS parentid,
                 cart.quantity AS quantity,
                 cart.pid AS pid,
                 cart.option_price AS option_price,
                 products.category AS category,
                 products.price AS price,
                 cart.sample AS sample,
    	         products.userseventeen AS collection,
                 coupons.minimum_qty AS minimum_qty
               FROM coupons
               LEFT OUTER
               JOIN cart ON cart.cart_id = 'xxxxxxx'
               LEFT OUTER
               JOIN products ON cart.pid = products.rowid
               LEFT OUTER
               JOIN categories ON categories.id = coupons.category
               WHERE coupons.code = 'XXXXXXXXXXXX'
               AND IF( coupons.expires IS NULL , TRUE, UNIX_TIMESTAMP( ) < coupons.expires )
               AND IF( coupons.uses =0, TRUE, coupons.used < coupons.uses )
               AND IF( coupons.category IS NULL, TRUE, products.category = coupons.category)
               GROUP BY coupons.id, cart.pid
               HAVING IF( coupons.minimum IS NULL , TRUE, coupons.minimum <= SUM( products.price * cart.quantity )  )
               AND IF( coupons.minimum_qty is NULL, TRUE, coupons.minimum_qty <= cart.quantity)
               LIMIT 0, 100
    ...Working with a limited amount of intelligence.
    --Kevin
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,685
    Rep Power
    4288
    you haven't told us what the unexpected results are

    just looking over your query, i would remove the GROUP BY since you aren't aggregating anything

    also, i would rewrite all those IFs as CASE expressions
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. intellectually challenged
    Devshed Novice (500 - 999 posts)

    Join Date
    Oct 2004
    Location
    m-w.com
    Posts
    664
    Rep Power
    408
    The resultset only contains one row, I'm expecting one row for every qualify item in the cart.
    ...Working with a limited amount of intelligence.
    --Kevin
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2015
    Location
    Colorado Springs, CO
    Posts
    20
    Rep Power
    0
    Just a thought, try moving the two HAVING statements up into the rest of the conditional expressions (before the GROUP BY)

    Comments on this post

    • MrFujin disagrees : I will recommend you to read the manual about the difference between WHERE and HAVING.
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,685
    Rep Power
    4288
    Originally Posted by ewish
    The resultset only contains one row, I'm expecting one row for every qualify item in the cart.
    well, that should be easy to research

    unfortunatyely i can't see your data from here

    ever set up an sqlfiddle?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. Lord of the Dance
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Oct 2003
    Posts
    4,118
    Rep Power
    2010
    Originally Posted by r937
    ... i would remove the GROUP BY since you aren't aggregating anything ...
    What about the SUM call in the HAVING clause?
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,685
    Rep Power
    4288
    Originally Posted by MrFujin
    What about the SUM call in the HAVING clause?
    good catch

    i guess i didn't see it because of my aversion to those fugly IFs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. Lord of the Dance
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Oct 2003
    Posts
    4,118
    Rep Power
    2010
    What is wrong with IF's? I thought this example is exactly where it should be used.
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,685
    Rep Power
    4288
    Originally Posted by MrFujin
    What is wrong with IF's?
    CASE is standard sql

    also, COALESCE can be even cleaner

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. Lord of the Dance
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Oct 2003
    Posts
    4,118
    Rep Power
    2010
    Was thinking about COALESCE, but couldn't see "the light".

    But with some afterthought, I guess this should be working:
    Code:
    COALESCE ( UNIX_TIMESTAMP( ) < coupons.expires , TRUE )
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,685
    Rep Power
    4288
    or this --
    Code:
    COALESCE(coupons.expires,'2999-12-31) > CURRENT_DATE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo