ranjan ranjan - 3 months ago 8
MySQL Question

MySQL :- Adding where clause to this query

I have 2 queries but I confused where to add where clause.

select supplier_id,count(*) c, party_name, SUM(po_value) po_value
from purchase_order join supplier s on s.id=supplier_id
group by supplier_id
having c>1 ORDER BY c DESC LIMIT 5


Second query

select warehouse_id, warehouse_name, SUM(available_quantity) available_quantity
from stock join warehouse s on s.id=warehouse_id
group by warehouse_id


In both the queries finally I have to fetch based upon a id like
where org_id='152'

Answer

See the edited query below:

select supplier_id,count(*) c, party_name, SUM(po_value) po_value
from purchase_order join supplier s on s.id=supplier_id

    WHERE purchase_order.org_id='152

group by supplier_id
having c>1 ORDER BY c DESC LIMIT 5

This compares against an org_id in purchase_order use s.org_id if you want to do the looking in the other table.

For the second one

select warehouse_id, warehouse_name, SUM(available_quantity) available_quantity
from stock join warehouse s on s.id=warehouse_id 

    WHERE org_id='152


group by warehouse_id 

(note that whitespace is collapsible). For full details see the manual section on select: http://dev.mysql.com/doc/refman/5.7/en/select.html

Comments