Click here to Skip to main content
15,885,366 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
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
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
Updated 21-Jan-23 23:23pm
0x01AA 22-Jan-23 4:57am    
I suggest to arrange the columns for both parts of the union statement to have _exactly_ the same order. After that try it again.
Golden Basim 22-Jan-23 5:19am    
thanks, the issue was with this table "TotalProfits"

1 solution

For UNION the order of the columns in the two SELECT counts.
Arranging the columns in consistent order will help to make it work as expected.
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