List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:February 21 2006 5:02pm
Subject:Re: help with query
View as plain text  
>how to achieve this?
>select table1.*, table2.*, table3.*, sum(table2.field3), sum(table2.field4)
>from table1, table2, table3 where table1.field1 = table2.field1 and
>table2.field1 = table3.field1

Your question as formulated has no answer. If you query aggregate values 
like Sum on a table, you will get back meaningful individual row values 
_only_ for fields which you Group By, so "table2.* makes no sense here.

So if the joins are to be on field1, and if you want to Group your Sums 
also By field1, your query would look like this:

SELECT table1.*, SUM(table2.field3), SUM(table2.field4)
FROM table1
INNER JOIN table2 USING (field1)
INNER JOIN table3 USING (field1)
GROUP BY table.field1;

PB

-----

xtcsuk wrote:
> 3 tables:
>
> table1 -> table2 (one to many)
> table2 -> table3 (one to one) [designed like this]
>
> how to achieve this?
> select table1.*, table2.*, table3.*, sum(table2.field3), sum(table2.field4)
> from table1, table2, table3 where table1.field1 = table2.field1 and
> table2.field1 = table3.field1
>
> regards
>
>   
> ------------------------------------------------------------------------
>
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.375 / Virus Database: 267.15.11/264 - Release Date: 2/17/2006
>   

Attachment: [text/html]
No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.11/264 - Release Date: 2/17/2006
Thread
help with queryxtcsuk21 Feb
  • Re: help with queryPeter Brawley21 Feb