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 ..

CREATE OR REPLACE VIEW `view_items_defin` as
       SELECT items.stitems_ID,
       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 ..
Updated 10-Mar-19 5:14am
[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 :
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