Using Join and Union to combine results from 2 tables [closed]

I already have a result set using join from multiple master tables with TABLE A as the primary table. Now I’m trying to perform union on table B with joins from masters retained.

This is the query I tried:

select 
    t1.* 
from 
    (select 
         id, mobile, email, pan 
     from a  
     union
     select  
         b_id, mobile, email, pan
     from b) as t1,
    ci.status,
    ab.desc
from 
    a 
left join 
    cuI ci on ci.id = a.id
left join 
    abMaster ab on ab.id = a.id
where 
    a.id is not null 
order by 
    a.created_on desc

This didn’t work

Answer

At first look your have several error

some column name ci.status, ab.descin wrong place,

two from clase

a reference at table a that should be (probably) a reference to t1

a possible valid query could be

select  t1.* , ci.status, ab.desc
from 
    (select id, mobile, email, pan 
     from a  
     union
     select b_id, mobile, email, pan
     from b
) as t1 
left join 
    cuI ci on ci.id = t1.id
left join 
    abMaster ab on ab.id = t1.id
where 
    t1.id is not null 
order by 
    t1.created_on desc