I’m trying to fetch records from temporary and permaenent tables using UNION . Table 1 – Cust Permanent
cid marital_status profession gender mobile current_status temp_id
1 1 6 Male 984343435 1 1
table 2 – cust temp
cid marital_status gender mobile current_status perm_id
1 1 Male 984343435 1 1
Also, trying to perform join from masters to fetch decription according to id. Performing a union query to fetch all records from permanent table, problem i’m facing is temp table will not have profession, but permanent table has and for union to work columns count should be same. Query i tried as a work around :
select cid,ma.master.desc, pr_master.desc,gender,mobile,current_status from cust_permament
left join ma_master on ma_master.id = cust_permament.marital_status
left join pr_master on pr_master.id = cust_permament.profession
UNION
select cid,ma.master.desc as marital_status, '' desc,gender,mobile,current_status from cust_temp
left join ma_master on ma_master.id = cust_temp.marital_status
This returns 2 rows one with empty profession and other with profession from permanent table. BUt i need single record with profession from permamnet table
Expected result
cid desc desc gender mobile current_status
1 Single Teacher Male 984343435 1
Actual Output
cid desc desc gender mobile current_status
1 Single Teacher Male 984343435 1
1 Single Male 984343435 1