Left Join & Count Issues

[coxdabd]

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

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!

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

[r937]

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?

[coxdabd]

Hey, right ok I'll get that one changed over to an INNER JOIN. Sure thing, the quantity is in the tmp_cart table.

[r937]

okay, you want to total up the quantities in the tmp_cart table

what do you need the products table for?

[coxdabd]

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?

[r937]

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