SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Evangelist
    Join Date
    Aug 2010
    Posts
    503
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Left Join & Count Issues

    Hi all, I appear to be having a couple of difficulties with my database query. When I use it as follows, it works perfectly.

    Code MySQL:
    SELECT * FROM tmp_cart AS t LEFT JOIN products AS p ON p.id=t.prod_id WHERE t.session_id='1431301305'

    However I want to add up all of the quantity fields to produce a total number of shopping cart items. I've used the following which does produce a total of all items but I no longer have all the rows, just one!

    Code MySQL:
    SELECT *,SUM(quantity) FROM tmp_cart AS t LEFT JOIN products AS p ON p.id=t.prod_id WHERE t.session_id='1431301305'

    Any help is appreciated as always

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    there are several issues with your query, not the least of which is your choice of using LEFT JOIN instead of INNER JOIN

    (using a LEFT JOIN from tmp_cart to products suggests that you are anticipating rows in tmp_cart which have no matching product, which, i'm gonna guess, would never happen)

    may i ask which table the quantity column is in?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Aug 2010
    Posts
    503
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey, right ok I'll get that one changed over to an INNER JOIN. Sure thing, the quantity is in the tmp_cart table.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    okay, you want to total up the quantities in the tmp_cart table

    what do you need the products table for?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist
    Join Date
    Aug 2010
    Posts
    503
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm just storing in tmp_cart the id of each product. So when the user wants to view their shopping basket I need to link up the tmp_cart ids to those of the product table. Any ideas?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by coxdabd View Post
    I'm just storing in tmp_cart the id of each product. So when the user wants to view their shopping basket I need to link up the tmp_cart ids to those of the product table. Any ideas?
    so to answer the question, you need the products table in order to retrieve columns like the product description and price

    presumably, you want to show these details on the "view cart" page, and yet at the same time, show the total quantity?

    the best technique for showing the total quantity would be to accumulate this while looping over the detail rows as you print them out

    you can't easily obtain details plus a total at the same time
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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