I try to Union two tables, but it doesn't work as expected.
Table 1:
purchases_main
Table 2:
purchases_preturned_main
table 2 has only two record, column "pretmain_ExtraValue" has zero value in the two record but the result of the view doesn't corrects (the values of columns "pretmain_ExtraValue" replaced with the values of columns "pretmain_NetValue") and also the reset of the table columns.
What I have tried:
CREATE OR REPLACE VIEW `view_purchases_and_returns` as
SELECT
purchases.pmain_Date as FullDateTime,
date(purchases.pmain_Date) as OpDate,
YEAR(purchases.pmain_Date) as OpYear,
DATE_FORMAT(purchases.pmain_Date, '%m %Y') as OpMonth,
WEEK(purchases.pmain_Date) as OpWeek,
DAY(purchases.pmain_Date) as OpDay,
purchases.r_user_ID as userID,
purchases.r_branche_ID as BranchID,
purchases.vendor_ID as VendorID,
purchases.r_shift_ID as shiftID,
1 as Qnt,
purchases.pmain_TotalValue as TotalValue,
purchases.pmain_ExtraValue as ExtraValue,
purchases.pmain_DiscValue as DiscValue,
purchases.pmain_NetValue as NetValue,
purchases.pmain_PaidValue as PaidValue,
purchases.pmain_ReminValue as ReminValue,
0 as rQnt,
0 as rTotalValue,
0 as rExtraValue,
0 as rDiscValue,
0 as rNetValue,
0 as rPaidValue,
0 as rReminValue,
0 as rTotalProfits
FROM purchases_main AS purchases
union all
SELECT
returnspurchases.pretmain_Date as FullDateTime,
date(returnspurchases.pretmain_Date) as OpDate,
YEAR(returnspurchases.pretmain_Date) as OpYear,
DATE_FORMAT(returnspurchases.pretmain_Date, '%m %Y') as OpMonth,
WEEK(returnspurchases.pretmain_Date) as OpWeek,
DAY(returnspurchases.pretmain_Date) as OpDay,
returnspurchases.r_user_ID as userID,
returnspurchases.r_branche_ID as BranchID,
returnspurchases.vendor_ID as CustID,
returnspurchases.r_shift_ID as shiftID,
0 as Qnt,
0 as TotalValue,
0 as ExtraValue,
0 as DiscValue,
0 as NetValue,
0 as PaidValue,
0 as ReminValue,
0 as TotalProfits,
1 as rQnt,
returnspurchases.pretmain_TotalValue as rTotalValue,
returnspurchases.pretmain_ExtraValue as rExtraValue,
returnspurchases.pretmain_DiscValue as rDiscValue,
returnspurchases.pretmain_NetValue as rNetValue,
returnspurchases.pretmain_PaidValue as rPaidValue,
returnspurchases.pretmain_ReminValue as rReminValue
FROM purchases_preturned_main AS returnspurchases