Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

ORDER BY problem with JOINs

Status
Not open for further replies.

rayfinkel2

Programmer
Feb 2, 2007
8
US
Here is my problem and it appears that it may only be a problem with PostgreSQL:

I am trying to order the products on my online store showing the products that have been sold the most amount of times within the last week on the top.

There are a lot of products that may not have been sold within the last week, so may not appear in the orders_prods table.

This is how I want them to be ordered:

prod_id-----qtysold
ghi-----------15
abc----------10
def-----------7
lmo-----------0
pqr-----------0
xyz-----------0



Here is my current query:

SELECT op.quantitySold, p.thumbnail, p.main_image, p.prod_price, p.prod_id, p.prod_name FROM products AS p INNER JOIN product_categories ON p.prod_id =

product_categories.prod_id LEFT OUTER JOIN (SELECT prod_id, SUM(quantity) AS quantitySold FROM orders_prods WHERE order_date > '$dateSevenDaysAgo' GROUP BY

prod_id) AS op ON op.prod_id = p.prod_id WHERE product_categories.cat_id = '$catID' ORDER BY op.quantitySold ASC

This Query orders the products like this:

prod_id-----qtysold
def-----------7
abc----------10
ghi-----------15
lmo-----------0
pqr-----------0
xyz-----------0


When I order quantitySold by DESC, the products that have been sold recently always appear on the bottom no matter what I try.

Here is an example query:

SELECT op.quantitySold, p.thumbnail, p.main_image, p.prod_price, p.prod_id, p.prod_name FROM products AS p INNER JOIN product_categories ON p.prod_id =

product_categories.prod_id LEFT OUTER JOIN (SELECT prod_id, SUM(quantity) AS quantitySold FROM orders_prods WHERE order_date > '$dateSevenDaysAgo' GROUP BY

prod_id) AS op ON op.prod_id = p.prod_id WHERE product_categories.cat_id = '$catID' ORDER BY op.quantitySold DESC, p.prod_id ASC

And I get this result:

prod_id-----qtysold
lmo-----------0
pqr-----------0
xyz-----------0
ghi-----------15
abc----------10
def-----------7


If anyone has any idea why this is happening, I would greatly appreciate the help.

Thank You,
Kyle
 
actually at first glance I don't see any reason, but why do you do that

SELECT op.quantitySold, ..... LEFT OUTER JOIN (SELECT prod_id, SUM(quantity) AS quantitySold FROM orders_prods WHERE order_date > '$dateSevenDaysAgo' GROUP BY

prod_id) AS op ON op.prod_id = p.prod_id

instead of

SELECT (SELECT SUM(quantity) AS quantitySold FROM orders_prods WHERE order_date > '$dateSevenDaysAgo' WHERE prod_id = p.prod_id) AS quantitySold ....



(as an idea try ORDER BY quantitySold - without the op.)
 
Thanks for the help ceco,

I am sorry, but I posted to so many forums that I forgot to put my final answer on this forum. The conclusion that I came to (with a lot of help from a couple of forums) is this final query. And it works great and seems to execute very quickly. What I forgot is that postgreSQL treats null values differently than zero, so I needed to use the COALESCE function.

SELECT op.quantitySold, p.thumbnail, p.main_image, p.prod_price, p.prod_id, p.prod_name FROM products AS p
INNER JOIN product_categories ON p.prod_id = product_categories.prod_id
LEFT OUTER JOIN (SELECT prod_id, SUM(quantity) AS quantitySold FROM orders_prods WHERE order_date > '$dateSevenDaysAgo' GROUP BY prod_id) AS op ON op.prod_id = p.prod_id
WHERE product_categories.cat_id = '$catID'
ORDER BY COALESCE(op.quantitySold, 0) ASC


Take care,
Kyle
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top