MySQL UNION is changing data format -


i'm little bit confused one. have similar tables bunch of different companies, trying union them combined data. however, union changes format of data 1 of columns, breaks php code utilizes column.

the column in each table is:

branch tinyint(2) unsigned zerofill 

here happens selecting first company (this example, actual select doing way more complex):

select distinct branch company1 +--------+ | branch | +--------+ |     01 | |     02 | |     03 | |     04 | |     40 | |     90 | +--------+ 

and second company:

select distinct branch company2 +--------+ | branch | +--------+ |     01 | |     02 | |     03 | |     04 | |     05 | |     40 | |     90 | +--------+ 

and union:

select distinct branch company1 union select distinct branch company2   +--------+ | branch | +--------+ |      1 | |      2 | |      3 | |      4 | |     40 | |     90 | |      5 | +--------+ 

you can see lose leading 0s on union. suggestions?

from mysql documentation:

the zerofill attribute ignored when column involved in expressions or union queries.

use lpad explicitly in queries:

select lpad(branch, 2, '0') branch (     select distinct branch company1      union      select distinct branch company2 ) x 

Comments

Popular posts from this blog

resizing Telegram inline keyboard -

command line - How can a Python program background itself? -

php - "cURL error 28: Resolving timed out" on Wordpress on Azure App Service on Linux -