SQL query with multiple joins a subqueries -

i understand sql query cannot dynamically add , remove columns. every site have same data. need data 'z' , tack on fields linked tables.

table tt_transaction , table tt_extendeddatatype independent. tt_extendeddatadetail has 2 foreign keys. 1) fk-> tt_transaction.id 2) fk -> tt_extendeddatatype

i need pull each transaction extended data. each site have same extended data names.

i think following broken code demonstrates trying better can explain.

select z.*, aaa.name, aaa.value, bbb.name, bbb.value tt_transaction left join (     select a.name, a.description, b.value, b.transaction_id text tt_extendeddatatype     inner join tt_extendeddatadetail b on a.id = b.type_id      a.name = 'number' ) aaa  on aaa.transaction_id = z.id left join (     select a.name, a.description, b.value, b.transaction_id text tt_extendeddatatype     inner join tt_extendeddatadetail b on a.id = b.type_id      a.name = 'string' ) bbb on bbb.transaction_id = z.id 

my current error column aaa.transaction_id not exist

select z.*, aaa.name, aaa.value, bbb.name, bbb.value tt_transaction left join (     select a.name, a.description, b.value, b.transaction_id transaction_id tt_extendeddatatype     inner join tt_extendeddatadetail b on a.id = b.type_id      a.name = 'number' ) aaa  on aaa.transaction_id = z.id left join (     select a.name, a.description, b.value, b.transaction_id text tt_extendeddatatype     inner join tt_extendeddatadetail b on a.id = b.type_id      a.name = 'string' ) bbb on bbb.transaction_id = z.id 

you named column text instead of transaction_id in subquery.