mysql - Joining 2 result sets to replace values in a column -

i have 2 sql result datasets need merged 1 like:

final merged table ------------- 320 sydney 321 brisbane 322 melbourne 

the sql achieve little more complicated since every data column saved in db separate record against user id (due wordpress , way stores 'meta_value' data)

first result set:

user table (a) ------------- 320 119  321 120  322 121  

achieved with:

select wp_frm_items.id, meta_value city  `wp_frm_items`   inner join `wp_frm_item_metas` on wp_frm_item_metas.item_id = wp_frm_items.id   `form_id` = 9 ,  field_id = 219 

second result set:

location table (b) ------------- 119 sydney 120 brisbane 121 melbourne 

achieved with:

select wp_frm_items.id, meta_value `wp_frm_items`   inner join `wp_frm_item_metas` on wp_frm_item_metas.item_id = wp_frm_items.id   `form_id` = 10 

mysql keeps telling me have error in final join code @ line 7 "inner join": (the above 2 sql statements work fine)

select *   (select wp_frm_items.id, meta_value city  `wp_frm_items`  inner join `wp_frm_item_metas` on wp_frm_item_metas.item_id = wp_frm_items.id  `form_id` = 9 ,  field_id = 219 )   inner join  select *   (select wp_frm_items.id, meta_value `wp_frm_items`  inner join `wp_frm_item_metas` on wp_frm_item_metas.item_id = wp_frm_items.id  `form_id` = 10) b   on a.city = b.id 

can shed light me on how can achieve this?

you're close. short answer don't need select * from in join clause, specify (derived) table itself:

select *   (select wp_frm_items.id, meta_value city  `wp_frm_items`  inner join `wp_frm_item_metas` on wp_frm_item_metas.item_id = wp_frm_items.id  `form_id` = 9 ,  field_id = 219 )   inner join     (select wp_frm_items.id, meta_value `wp_frm_items`      inner join `wp_frm_item_metas` on wp_frm_item_metas.item_id = wp_frm_items.id      `form_id` = 10) b   on a.city = b.id