Message-ID: <1059478450.3f265bb210e14@stud.fbi.fh-darmstadt.de>
Date: Tue, 29 Jul 2003 13:34:10 +0200
From: oheinz@stud.fbi.fh-darmstadt.de
To: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: Re: Problem using Subselect results
References: <1058964708.3f1e84e407a8d@stud.fbi.fh-darmstadt.de>
	<200307280947.21658.josh@agliodbs.com>
In-Reply-To: <200307280947.21658.josh@agliodbs.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
User-Agent: Internet Messaging Program (IMP) 3.2.1

> Although in the simplistic examples above there's not much reason to use a 
> subselect at all, of course.

O.K. my fault - and the subselects,  now that i know not to use them on the 
same level, seem not to be my real problem.

Another (hopefully better) example to show you what I was trying to achieve:

Three tables:

Table 'one' references table 'two', table 'two' references table 'three'

So creating a view which contains the corresponing data would be something 
like: 

CREATE VIEW data AS SELECT two_value, three_value FROM ((one JOIN two ON 
((one.two_id = two.two_id))) JOIN three ON ((two.three_id = three.three_id)));

But as this data is time sensitive, we introduce some kind of time stamp - a 
serial which is global to all tables. Now, for each record in table 'one' i 
want to see only the corresponding records in tables two, three, etc... that 
were created before 'one.updatenr'

SELECT * FROM one, two WHERE (one.two_id=two.two_id AND one.updatenr > 
two.updatenr);

This might match multiple records in tables two (two_id is not a pk, we have 
historic records in this table). Now I want only the most current version 
before one.updatenr. - And that's where I run into trouble. (that's why i 
constructed those awful subselects)

with 'max()' and 'order by updatenr desc limit 1;' I limit results to one 
value  - but I need one maximum for each one_id=two_id
Any ideas on how to do this is in SQL?


Same then with table 'three', it is referenced by table 'two' but updatenr is 
restricted by one.updatenr.


TIA,
Oliver


For those who want to help this is the examples table structure:

-- Sequence: public.updatenr
CREATE SEQUENCE public.updatenr INCREMENT 1 MINVALUE 1 MAXVALUE 
9223372036854775807 CACHE 1;


-- Table: public.one
CREATE TABLE public.one (
  one_id int4, 
  two_id int4, 
  updatenr int4 DEFAULT nextval('public."updatenr"'::text) NOT NULL
) WITH OIDS;

-- Table: public.two
CREATE TABLE public.two (
  two_id int4 NOT NULL, 
  two_value varchar(256), 
  three_id int4 NOT NULL, 
  updatenr int4 DEFAULT nextval('public."updatenr"'::text) NOT NULL
) WITH OIDS;

-- Table: public.three
CREATE TABLE public.three (
  three_id int4 NOT NULL, 
  three_value varchar(256), 
  updatenr int4 DEFAULT nextval('public."updatenr"'::text) NOT NULL
) WITH OIDS;

-------------------------------------------------
This mail sent through IMP: http://horde.org/imp/