[bmacuer]

SELECT product.product_id, product.product_brand_id, product.product_model_id, product.product_subcategory_id, product.product_retail_price, product.product_wholesale_price, SUM(product_sold.product_quantity) AS product_quantity_sold, SUM(product_sold.product_total_price) AS total_price_sold FROM product
						LEFT JOIN product_sold ON product.product_id = product_sold.product_id
							LEFT JOIN sales ON sales.sales_id = product_sold.product_sales_id WHERE sales.sales_approved = '1' AND sales.sales_approved_time > '$start_timestamp' AND sales.sales_approved_time < '$end_timestamp'
								GROUP BY product.product_id ORDER BY SUM(product_sold.product_quantity) DESC

What I want is to display products even if they havent been sold, I originally had INNER JOIN then I changed it to LEFT JOIN thinking that it will display all the product from PRODUCT table and order them by the quantity sold and display non sold products at the end. Well I hope I can get some help I been doing this query in different ways but havent come close to what I want, Thanks in advanced. Bryan

[e_i_pi]

The LEFT JOIN will ensure that all products are represented at least once, but you're using a WHERE clause, which will counteract that. You can easily fix this by changing the keyword WHERE to ON (in this case) to attach the conditions to the LEFT JOIN of the sales table:

SELECT
	product.product_id,
	product.product_brand_id,
	product.product_model_id,
	product.product_subcategory_id,
	product.product_retail_price,
	product.product_wholesale_price,
	SUM(product_sold.product_quantity) AS product_quantity_sold,
	SUM(product_sold.product_total_price) AS total_price_sold
FROM product
LEFT JOIN product_sold ON product.product_id = product_sold.product_id
LEFT JOIN sales
	ON sales.sales_id = product_sold.product_sales_id
	AND sales.sales_approved = '1'
	AND sales.sales_approved_time > '$start_timestamp'
	AND sales.sales_approved_time < '$end_timestamp'
GROUP BY product.product_id
ORDER BY SUM(product_sold.product_quantity) DESC

I'm not sure your GROUP BY clause will work as intended there. I generally find that it is necessary to GROUP BY every selected column that isn't aggregated. You might need to change it to this:

GROUP BY
	product.product_id,
	product.product_brand_id,
	product.product_model_id,
	product.product_subcategory_id,
	product.product_retail_price,
	product.product_wholesale_price

[bmacuer]

Thanks for the help it was very useful I had to add a WHERE CLAUSE though because it was overloading the server once I wanted to create the report, it might be because the are many items sold and also products, so this was the code that worked for me.

SELECT 
  product.product_id, 
  product.product_brand_id, 
  product.product_model_id, 
  product.product_subcategory_id, 
  product.product_retail_price, 
  product.product_wholesale_price, 
  SUM(product_sold.product_quantity) AS product_quantity_sold, 
  SUM(product_sold.product_total_price) AS total_price_sold 
FROM product
LEFT JOIN product_sold 
  ON product.product_id = product_sold.product_id 
  AND product.product_brand_id = '$brand_id' 
  AND product.product_model_id = '$model_id' 
  AND product.product_subcategory_id = '$subcategory_id'
LEFT JOIN sales 
  ON sales.sales_id = product_sold.product_sales_id 
  AND product_sold.product_warehouse_id = '$warehouse_id' 
  AND sales.sales_approved = '1' 
  AND sales.sales_approved_time > '$start_timestamp' 
  AND sales.sales_approved_time < '$end_timestamp'
WHERE 
  product.product_brand_id = '$brand_id' 
  AND product.product_model_id = '$model_id' 
  AND product.product_subcategory_id = '$subcategory_id' 
GROUP BY 
  product.product_id 
ORDER BY 
  SUM(product_sold.product_quantity) DESC