Click here to Skip to main content
15,499,155 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I don't want to see a stock of products that are empty.
 I can't figure out my mistake " where condition".
Thanks in advance for the solution.

What I have tried:

select a.Company_Name as Company, 
           a.Product_name as product,
          stock =ISNULL( a.qty,0) - isnull(b.qty, 0)-isnull(c.Qty,0)
    from   (
            select Company_Name, Product_name, qty = sum(Qty)
            from   tbl_purchase
             group by Company_Name, Product_name
           ) a
           left join 
           (select Company_Name, Product_name,qty = sum(qty)
             from  tbl_sales
    		   group by Company_Name, Product_name
           ) b     on  a.Company_Name = b.Company_name
                 and a.Product_name = b.Product_name
	left join
	select Company_Name,Product_Name,Qty=sum(Damage_Qty)
	from tbl_damage
	group by Company_Name, Product_name
	) c
	on a.Company_Name=c.Company_Name
	and a.Product_Name=c.Product_Name
	<pre>	where stcok <> 0
Updated 16-Aug-21 0:53am

Regardless of the spelling error, the field 'stock' doesn't exist yet when where stcok <> 0 is evaluated.
WHERE ISNULL( a.qty,0) - isnull(b.qty, 0)-isnull(c.Qty,0) <> 0
Share this answer
jewel serniabad 16-Aug-21 10:47am    
stock =ISNULL( a.qty,0) - isnull(b.qty, 0)-isnull(c.Qty,0)

where stcok <> 0 /* You have a spelling error here. */
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900