Re: Join Question

From: Andy Triggs <andrew.triggs_at_businessobjects.com>
Date: 28 Jan 2003 06:27:45 -0800
Message-ID: <2b6e86d0.0301280627.ef4a4ab_at_posting.google.com>

The query you used in your original posting was almost correct.

Since there is a one-to-many relationship between table1 and table2, the amount field in table1 should be included in the GROUP BY clause and only the amount from table2 requires summing. Note that in the example given, the outer join is not strictly necessary either as all ids in table1 are represented in table2:

SELECT

	(a.id)as x,
	a.amount y,
	sum(b.amount)as z

FROM table1 a, table2 b
WHERE a.id = b.id
GROUP BY a.id, a.amount

regards, Andy

"Brian E Dick" <bdick_at_cox.net> wrote in message news:<XodZ9.37213$GX4.1415570_at_news2.east.cox.net>...
> Sorry, in my haste to produce your results I didn't see the no subquery
> restriction. Why no subquery?
Received on Tue Jan 28 2003 - 15:27:45 CET