Month: May 2018

Sql queries to use Alias in where – alternative

I have been working on SQL queries. Now the scenario is as follows.

what if you wanted to compare the alias in WHERE. By mysql rules, you cannot use Alias in WHERE. However, you can use alias to filter the results using HAVING. The difference between WHERE and HAVING clause is that you can use WHERE in UPDATE or DELETE clause, while HAVING is only used when to filter the results

I have one to many relationships in table (lets say we have items at multiple locations).

Now I have to pull a low stock report where total item quantities at multiple locations is less than threshold. Thus I have to sum the total quantities at multiple locations on fly and compare it with recommended minimum level of quantities.

I am sure there could be other problems to this approach but I have used the following code.


   SELECT as master_item_id, as item_name, as supplier_name,
item.recommended_lowest_quantity as min_level,
(SELECT SUM(quantity_out) FROM item_outbound WHERE AND item_outbound.status_id IN  (6, 7 , 12, 20 ,21)) as total_qty,
item_outbound.quantity_out as location_qty, as outbound_id, as location_name, as status_name
FROM item
LEFT JOIN item_outbound ON
LEFT JOIN supplier ON
LEFT JOIN location ON
AND item_outbound.status_id IN (6, 7 , 12, 20 ,21)
HAVING total_qty <min_level
ORDER BY `master_item_id` ASC