how to force MySQL VIEW show all records even if the left table don't have any matches records.
What I have tried:
my code that i tried :
CREATE OR REPLACE VIEW `view_shortages_with_store` as
SELECT st.store_ID,
st.store_Name,
sti.stitems_Name,
sti.stitems_Type,
sti.stcate_ID,
sti.stitems_EpireDateStatus,
sti.stitems_MinBalance,
sti.stitems_stagnant,
IF(ISNULL(ca.stitems_ID), pa.stitems_ID, ca.stitems_ID)
AS stitems_ID,
COALESCE(ca.Amount, 0) + COALESCE(pa.Amount,0) AS Stock
FROM st_stores st
LEFT JOIN (SELECT sns.stitems_ID,
sns.StoreID,
SUM(sns.StockQnty) AS Amount
FROM stock_noserials sns
GROUP BY sns.stitems_ID, sns.StoreID) ca
ON ca.StoreID = st.store_ID
LEFT JOIN (SELECT pis.stitems_ID,
pis.ss_StoreID,
COUNT(*) AS Amount
FROM purchases_item_seriels pis
WHERE pis.pis_Statues IN (0, 5, 6)
GROUP BY pis.stitems_ID , pis.ss_StoreID) pa
ON pa.ss_StoreID = st.store_ID
INNER JOIN (SELECT sts.stitems_ID,
sts.stitems_Name,
sts.stitems_Type,
sts.stcate_ID,
sts.stitems_EpireDateStatus,
sts.stitems_MinBalance,
sts.stitems_stagnant
FROM st_items sts
GROUP BY sts.stitems_ID) sti
ON sti.stitems_ID = ca.stitems_ID ||sti.stitems_ID = pa.stitems_ID
WHERE COALESCE(ca.Amount, 0) + COALESCE(pa.Amount,0) < sti.stitems_MinBalance
GROUP BY st.store_ID,sti.stitems_ID,COALESCE(ca.Amount, 0) + COALESCE(pa.Amount,0);
Ex for what i need :
if (item A) have stock (5) in (Store 1), and have stock(0) _there is no records_ in (Store 2) show this results:
----------------------------------------------------
store | item | stock
1 | item A | 5
2 | item A | 0
3 | item A | 0
4 | item A | 0
----------------------------------------------------