Click here to Skip to main content
15,917,060 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hi, i use this code to create Mysql View that sum the items stock ..

SQL
CREATE OR REPLACE VIEW `view_items_defin` as
       SELECT items.stitems_ID,
       items.stitems_Status,
       items.stitems_Name,
       items.stitems_Pharma_ActiveIngredient,
       items.stitems_Code,
       manuf.manu_Name,
       COALESCE(ca.Amount, 0) + COALESCE(pa.Amount,0) AS Stock
FROM st_items items
LEFT JOIN (SELECT sns.stitems_ID,
                  SUM(sns.StockQnty) AS Amount
           FROM stock_noserials sns
           GROUP BY sns.stitems_ID) ca
ON ca.stitems_ID = items.stitems_ID
LEFT JOIN (SELECT pis.stitems_ID,
                  COUNT(*) AS Amount
           FROM purchases_item_seriels pis
           WHERE pis.pis_Statues IN (0, 5, 6)
           GROUP BY pis.stitems_ID) pa
ON pa.stitems_ID = items.stitems_ID

INNER JOIN `st_plug_manufacturer` AS manuf
ON items.`stitems_Manufacturer` = manuf.`manu_ID`;


this are tables data :
1- stock_noserials table
---------------------------------
id | stitems_ID     | StockQnty | ---
1  | 15078          | -6        | ---
2  | 15078          | 0         | ---
3  | 15078          | 0         | ---
---------------------------------


and there is no data in purchases_item_seriels table

but the result of the view code :Stock = -2 not -6

What I have tried:

i tried to follow many items but the same problem ..
Posted
Updated 10-Mar-19 5:14am
v2
Comments
[no name] 10-Mar-19 10:57am    
Nobody knows what's in the tables except you. And the more complicated you make a query, the less anybody else can help you. You should check the intermediate results before coming up with just a "pile of SQL" you don't understand.

1 solution

Try changing the following line to something else :
SQL
...
COUNT(*) AS Amount
...
 
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