mysql - query to get column values based on values of another table i.e,one table values having as field names in another table -

mysql - query to get column values based on values of another table i.e,one table values having as field names in another table -

i have 2 table hrm_m_allowance,pay_m_allowance.

hrm_m_allowance having fields like...

int_appid int_basic a0001 a0002 a0003 a0004 a0005 --------------------------------------------------------- 14 7900 1200 700 2000 1000 500

pay_m_allowance having fields like...............

chr_acode chr_aname chr_bonus .......etc ---------------------------------------------- a0001 hra 0 a0002 da 0 a0003 pf 0 a0004 esi 0

now have values like

basic 7900 hra 1200 da 700 pf 2000 esi 1000

please help me how values above using mysql query

funky database design. suggest unpivoting info first , doing join:

select coalesce(pb.chr_aname, col) chr_aname, h.val (select 'int_basic' col, int_basic val hrm_m_allowance union select 'a0001' col, a0001 val hrm_m_allowance union select 'a0002' col, a0002 val hrm_m_allowance union select 'a0003' col, a0003 val hrm_m_allowance union select 'a0004' col, a0004 val hrm_m_allowance union select 'a0005' col, a0005 val hrm_m_allowance ) h left bring together pay_m_allowance pb on pb.chr_acode = h.col;

one caveat approach: work best when columns have same info type.

mysql sql