--- In firebird-support@yahoogroups.com, "David Wessell" <dave@...> wrote:

>
> Hi..
>
> In this specific instance I'm working with two tables, and trying to
> get some aggregate information out of one of them.
>
> The two tables are header and detail. The primary key of header is
> header_auto_key, and that appears in the detail table as a foreign key.
>
> When I run the query: SELECT sum(total_price) FROM header; I get a
> correct result.
>
> If I run: SELECT sum(total_price) FROM header, detail WHERE
> header.header_auto_key = detail.header_auto_key; I get an incorrect
> result.
>
> With the last example, duplicate values (total_price) are returned
> from the header table. The total_price field does not exist in the
> detail table.
>
> It's a long drawn out story, why I would need to run a query like the
> second option. But I thought someone might have a suggestion as to how
> I could restructure that second query, to filter out duplicate results..

I was using DOS 6 when that syntax was depricated.

Try:

SELECT sum(header.total_price)
FROM header
join detail on (header.header_auto_key = detail.header_auto_key);

Separating the conditions from the join conditions makes your queries
more readable.

In terms of your duplicates, I highly doubt that they come from
headers with no detail records, they probably come from headers with
multiple detail lines.

An inner join (which is what you have defined in SQL89) will make a
product of any left and right combination where there is a match, so
if you have two detail lines with the same header_auto_key, that
header_auto_key will be duplicated, exactly what you asked for.

Or look at it another way.

You want it to sum each header record once, so what is it supposed to
do when you give it a header record with two detail records hanging
off it? Which detail record should it return?

Perhaps explain your goal and the right query may become obvious.

Adam