[Info-Ingres] Few questions concerning Ingres 2006

[Marek.Patrzek at gmail.com]

On 9 Kwi, 21:23, "Roy Hann" <specia... at processed.almost.meat> wrote:
> <Marek.Patr... at gmail.com> wrote in message
>
> news:1176138756.104459.54830 at w1g2000hsg.googlegroups.com...
>
> >> Subqueries in the FROM clause are supported as of Ingres 2006 Release 2,
> >> and
> >> they require the use of the correlation name in the target list.  Try:
>
> >> SELECT tmp.* FROM...
>
> >> Roy
>
> > Thanks for your replay, I will try that on tuesday.
>
> > However, as I read that in the official sql reference, is using
> > aggregate functions in the subquery forbidden ?
>
> No, definitely not.  Apart from the HAVING clause, a subquery is the most
> obvious place to expect to be abke to use an aggregate.  Do you have a
> specific query in mind?
>
> Roy

Well I do, however it's hard to put you in the picture without having
exact schema. I try to explain giving similar example.

Let's assume, that we have table that holds info concerning
correspondence (like ID, signature, author etc.), a table that holds
attachments to correspondences (ID, correspondence_id, size etc).

I want to know how show all data for a specific correspondence and
size of its attachments. The simplest way, to that is by doing:

SELECT
	correspondence.*,
	ata_cnt.cnt
FROM
	correspondence
	JOIN
	(
		SELECT
			correspondence_id,
			SUM (size) AS cnt
		FROM
			attachment
		GROUP BY
			correspondence_id
	) ata_cnt ON correspondence.id = ata_cnt.correspondence_id

Please, do not focus @ this query's efficiency, as it's not this
issue's clue :) All I can say, is that, this particular way of
retrieving data is the way it has to be done, as my real life case,
which is far more complicated, force it.