SQL query about union and concat

2661 views mysql
0

I need to select union values from two table and also add prefix to the result values. Eg:-

select concat('Source-',id), concat('Source-',name) from src_tbl where id IS not NULL and name IS not NULL    
UNION
select concat('Destination-',id), concat('Destination-',name) from dstn_table where id IS not NULL and name IS not NULL  
order by name

Union and concat is working separately but wen I am combining it's not working and throwing error "that name is not found from tables on both sides of union". The column is present though

answered question

2 Answers

12

use alias in 1st table column name

  select concat('Source-',id) source_id,
         concat('Source-',name) name
         from src_tbl where id IS not NULL and name IS not NULL    
         UNION
  select concat('Destination-',id),
         concat('Destination-',name)
         from dstn_table where id IS not NULL and name IS not NULL  
         order by name

posted this
0

you need to define alias for column name and define order by either each query or you can define order by after merging both query - like below

select * from
(
select concat('Source-',id) as id, concat('Source-',name) as name from src_tbl where id IS not NULL and name IS not NULL    
UNION
select concat('Destination-',id), concat('Destination-',name) from dstn_table where id IS not NULL and name IS not NULL  
) order by name

posted this

Have an answer?

JD

Please login first before posting an answer.