Trying to perform union when columns dont match

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

More Related Articles