Dear All, I have bit of SQL (mysql) which joins three tables to get all the records form the first table and sums from the 2 remaining tables. The SQL looks like:
select t1.*, t2_total, t3_total from t1 left join (select t1.id as t1_id, sum(t2.total) as t2_total from t1 left join t2 on t2.t1_id=t1.id group by t1.id) as t2s on t1.id = t2s.t1_id left join (select t1.id as t1_id, sum(t3.total) as t3_total from t1 left join t3 on t3.t1_id=t1.id group by t1.id) as t3s on t1.id = t3s.t1_id group by t1.id; This works fine. I reckon (obviously wrongly) that this translates to: my @join = $schema->resultset('T1')->search({ }, { +select => ['id' \['SUM(T2.total)'], \['SUM(T3.total)']], as => [qw /id t2_total t2_total/ ], join => ['T2', 'T3'], group_by => 'id', }); The search returns a resultset OK but the sums are multiplied by the number of permutations, e.g. SUM(T2.total) is a factor of 3 high if there 3 results in T3. I think the question I may be asking is what is the syntax to express a 3 table join, on T1 to T2 and T1 to T3 type arrangement. Thanks, Scott _______________________________________________ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk