> 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/
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
