Databases
StackOverflow2013
Postsdbo
POHAVING clause affecting results
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHAVING clause affecting results
    text
    copied!<p>I have a query which shows sold and unsold products.</p> <p>below is the query which is working correctly.</p> <pre><code> 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 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 &gt; '$start_timestamp' AND s.sales_approved_time &lt; '$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 </code></pre> <p>but onced added having to filter the list to only show</p> <p>product that have stock or products that have been sold only, with this query below:</p> <pre><code> 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 &gt; '$start_timestamp' AND s.sales_approved_time &lt; '$end_timestamp' ) ) AND pb.brand_name NOT LIKE 'X%' GROUP BY p.product_id HAVING total_stock &gt; 0 OR product_quantity_sold &gt; 0 ORDER BY product_quantity_sold DESC, pb.brand_name ASC, pm.model_name ASC </code></pre> <p>Its kinda weird because im getting different results on each product for the variable below</p> <pre><code>product_quantity_sold </code></pre> <p>everything else in the result stays the same.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload