> many times i need to return more then one field from subquery but this is > not possible and then i must do some join and group by or write stored proc > or other trick like concat fields by "||" > > My question is "Is this because of SQL standard or this is not simple to > develop?" > > > sample > > SELECT > TA.Field_A > ,TA.Field_B > > ,(SELECT TB.FIELD_C, TB.FIELD_D, SUM(TB.FIELD_E), MAX(TB.FIELD_F), ... > FROM TABLE_B TB WHERE ...) /* we can only select single field TB.FIELD_C > but not TB.FIELD_C, TB.FIELD_D, SUM(TB.FIELD_E), MAX(TB.FIELD_F) ... > > FROM > TABLE_A TA
You should be able to use a Common Table Expression (CTE), an example would be: WITH SubQuery AS ( SELECT TB.FIELD_C, TB.FIELD_D, SUM(TB.FIELD_E) as SUM_Field_E, MAX(TB.FIELD_F) as Max_Field_F, ... FROM TABLE_B TB WHERE ... ) SELECT TA.Field_A ,TA.Field_B ,SubQuery.Field_C ,SubQuery.Field_D ,SubQuery.SUM_Field_E ,SubQuery.MAX_Field_F FROM Table_A TA JOIN SubQuery ON SubQuery.Field_C = TA.Field_C Note an alias is required for computed columns in the SubQuery. Please see the README.common_table_expressions in the Doc\SQL.Extensions folder of the Firebird install kit for exact syntax/usage. Sean ------------------------------------------------------------------------------ DreamFactory - Open Source REST & JSON Services for HTML5 & Native Apps OAuth, Users, Roles, SQL, NoSQL, BLOB Storage and External API Access Free app hosting. Or install the open source package on any LAMP server. Sign up and see examples for AngularJS, jQuery, Sencha Touch and Native! http://pubads.g.doubleclick.net/gampad/clk?id=63469471&iu=/4140/ostg.clktrk Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
