CODEWITHSUNDEEP
mysqlsqldatabaseleft-join
bmacuer
bmacuer

Reputation: 37

Unwanted query results

Im doing a report for sold and unsold items on a system I created with this query below every is working fine im getting products that are sold and unsold.

SELECT p.product_id, p.product_brand_id, p.product_model_id, p.product_subcategory_id, 
       p.product_retail_price, p.product_wholesale_price,
       SUM(IFNULL(ps.product_quantity,0)) AS product_quantity_sold,
       SUM(IFNULL(ps.product_total_price,0)) AS total_price_sold,
       pb.brand_name, pm.model_name, psub.subcategory_name
 FROM product p
 LEFT JOIN product_sold ps ON p.product_id = ps.product_id
 LEFT JOIN sales s ON ps.product_sales_id = s.sales_id
 JOIN product_brand pb ON pb.brand_id = p.product_brand_id
 JOIN product_model pm ON pm.model_id = p.product_model_id
 JOIN product_subcategory psub ON psub.subcategory_id = p.product_subcategory_id
   WHERE ( s.sales_id IS NULL
   OR ( s.sales_approved = '1' 
      AND s.sales_approved_time > '$start_timestamp'  
      AND s.sales_approved_time < '$end_timestamp'
      )
   )
   AND pb.brand_name NOT LIKE 'X%'
 GROUP BY p.product_id 
 ORDER BY product_quantity_sold DESC, pb.brand_name ASC, pm.model_name ASC

But what I want is to limit the results to

if item has been sold and has 0 stock - show it in the results.

if item has stock but hasnt been sold - show it in the results.

if item has no stock and has no sells - dont show it.

I tried doing in it with this query.

 SELECT p.product_id, p.product_brand_id, p.product_model_id, p.product_subcategory_id, p.product_retail_price, p.product_wholesale_price, SUM(IFNULL(ps.product_quantity,0)) AS product_quantity_sold,SUM(IFNULL(ps.product_total_price,0)) AS total_price_sold, SUM(IFNULL(pq.product_quantity,0)) AS total_stock, pb.brand_name, pm.model_name, psub.subcategory_name
 FROM product p
 LEFT JOIN product_sold ps ON p.product_id = ps.product_id
 LEFT JOIN sales s ON ps.product_sales_id = s.sales_id
 LEFT JOIN product_stock pq ON p.product_id = pq.product_id
 JOIN product_brand pb ON pb.brand_id = p.product_brand_id
 JOIN product_model pm ON pm.model_id = p.product_model_id
 JOIN product_subcategory psub ON psub.subcategory_id = p.product_subcategory_id
   WHERE ( s.sales_id IS NULL
        OR ( s.sales_approved = '1' 
           AND s.sales_approved_time > '$start_timestamp'  
           AND s.sales_approved_time < '$end_timestamp'
      )
   )
   AND pb.brand_name NOT LIKE 'X%' 
   AND ((pq.product_id IS NOT NULL OR SUM(pq.product_quantity) != 0)  AND (ps.product_quantity != 0))
 GROUP BY p.product_id 
 ORDER BY product_quantity_sold DESC, pb.brand_name ASC, pm.model_name ASC

But is failing.

FIXED!! I GOT IT WORKING WITH THIS QUERY

SELECT p.product_id, 
    p.product_brand_id, 
    p.product_model_id, 
    p.product_subcategory_id,
    p.product_retail_price, 
    p.product_wholesale_price, 
    SUM(IFNULL(ps.product_quantity,0)) AS product_quantity_sold,
    SUM(IFNULL(ps.product_total_price,0)) AS total_price_sold, 
    SUM(IFNULL(pq.product_quantity,0)) AS total_stock, 
    pb.brand_name, 
    pm.model_name, 
    psub.subcategory_name
FROM product p
LEFT JOIN product_sold ps ON p.product_id = ps.product_id
LEFT JOIN sales s ON ps.product_sales_id = s.sales_id
LEFT JOIN product_stock pq ON p.product_id = pq.product_id
JOIN product_brand pb ON pb.brand_id = p.product_brand_id
JOIN product_model pm ON pm.model_id = p.product_model_id
JOIN product_subcategory psub ON psub.subcategory_id = p.product_subcategory_id
WHERE p.product_brand_id = $brand_id AND p.product_model_id = $model_id
AND ( s.sales_id IS NULL
   OR ( s.sales_approved = '1' 
      AND s.sales_approved_time > '$start_timestamp'  
      AND s.sales_approved_time < '$end_timestamp'
      )
   )
  AND pb.brand_name NOT LIKE 'X%'
GROUP BY p.product_id 
HAVING SUM(IFNULL(pq.product_quantity,0)) > 0 OR SUM(IFNULL(ps.product_quantity,0)) > 0
ORDER BY product_quantity_sold DESC, pb.brand_name ASC, pm.model_name ASC

Upvotes: 0

Views: 67

Answers (2)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

You need to use HAVING clause to filter results with aggregate functions

SELECT p.product_id,
p.product_brand_id,
p.product_model_id, 
p.product_subcategory_id, 
p.product_retail_price, 
p.product_wholesale_price, 
SUM(IFNULL(ps.product_quantity,0)) AS product_quantity_sold,
SUM(IFNULL(ps.product_total_price,0)) AS total_price_sold, 
SUM(IFNULL(pq.product_quantity,0)) AS total_stock,
pb.brand_name, pm.model_name,
psub.subcategory_name
 FROM product p
 LEFT JOIN product_sold ps ON p.product_id = ps.product_id
 LEFT JOIN sales s ON ps.product_sales_id = s.sales_id
 LEFT JOIN product_stock pq ON p.product_id = pq.product_id
 JOIN product_brand pb ON pb.brand_id = p.product_brand_id
 JOIN product_model pm ON pm.model_id = p.product_model_id
 JOIN product_subcategory psub ON psub.subcategory_id = p.product_subcategory_id

 WHERE ( s.sales_id IS NULL
        OR ( s.sales_approved = '1' 
           AND s.sales_approved_time > '$start_timestamp'  
           AND s.sales_approved_time < '$end_timestamp'
      )
   )
   AND pb.brand_name NOT LIKE 'X%' 
   GROUP BY p.product_id 
   HAVING  ((pq.product_id IS NOT NULL OR total_stock != 0) 
   AND (ps.product_quantity != 0))
 ORDER BY product_quantity_sold DESC, pb.brand_name ASC, pm.model_name ASC

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You want to add the following having clause just before the order by:

having SUM(IFNULL(pq.product_quantity,0)) > 0 or SUM(IFNULL(ps.product_quantity,0)) > 0

You probably want to add this to the first version. I don't totally follow the logic in the second version.

Upvotes: 1

Related Questions