Thread: Problem using Subselect results
I want to use the result of a subselect as condition of another one. CREATE VIEW my_view AS SELECT b,c (SELECT a, b FROM table2 WHERE b=1) my_ab, (SELECT c FROM table3, my_ab WHERE table3.a=my_ab.a) my_c; does return "relation my_ab unknown". it is not just a problem of execution order - if i turn it the other way round it's still the same. Am I just trying to do something really stupid? And what for is the (necessary) AS statement for subselects, if it's not possible to access their results by that name? As I need the result of a subselect in several other subselects it's not possible to transform them into a cascade of sub, subsub, subsubsub.... selects. Any ideas? TIA, Oliver ------------------------------------------------- This mail sent through IMP: http://horde.org/imp/
On Wed, Jul 23, 2003 at 14:51:48 +0200, oheinz@stud.fbi.fh-darmstadt.de wrote: > I want to use the result of a subselect as condition of another one. The two selects you use ar both from items at the same level and hence can't reference one another. In your example below you could just use a join. > CREATE VIEW my_view AS SELECT b,c > (SELECT a, b FROM table2 WHERE b=1) my_ab, > (SELECT c FROM table3, my_ab WHERE table3.a=my_ab.a) my_c; Something like: CREATE VIEW my_view AS SELECT table2.b, table3.c from table2, table3 where table2.b = 1 and table2.a = table3.a;
Oliver, > CREATE VIEW my_view AS SELECT b,c > (SELECT a, b FROM table2 WHERE b=1) my_ab, > (SELECT c FROM table3, my_ab WHERE table3.a=my_ab.a) my_c; This isn't possible in PostgreSQL, and I'm not sure it's possible anywhere. HOWEVER, if you put your subselects in the FROM clause instead, like so: CREATE VIEW my_sub AS SELECT my_ab.a, my_ab.b, my_c.c FROM (SELECT a, b FROM table2 WHERE b=1) my_ab,(SELECT a,c FROM table3, my_ab) my_c WHERE my_ab.a = my_c.a; OR you can mix-and-match subselect types: CREATE VIEW my_sub AS SELECT my_ab.a, my_ab.b,(SELECT c FROM table3, my_ab WHERE table3.a=my_ab.a) my_c FROM (SELECT a, b FROM table2 WHERE b=1) my_ab; Although in the simplistic examples above there's not much reason to use a subselect at all, of course. -- Josh Berkus Aglio Database Solutions San Francisco
> 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
DEFAULTnextval('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/
oheinz@stud.fbi.fh-darmstadt.de writes:
> 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.
You might be able to make this work by using SELECT DISTINCT ON. See
the "weather reports" example in the SELECT reference page.
regards, tom lane
I'll try this tomorrow - combining DISTINCT ON (two.two_id) and sorting by two.updatenr could (should) have the desired effect - I never thought about using ORDER and DISTINCT that way. I'll report my success or failure... Thanks so far! Bye, Oliver ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: <oheinz@stud.fbi.fh-darmstadt.de> Cc: <pgsql-sql@postgresql.org> Sent: Tuesday, July 29, 2003 5:00 PM Subject: Re: [SQL] Problem using Subselect results > oheinz@stud.fbi.fh-darmstadt.de writes: > > 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. > > You might be able to make this work by using SELECT DISTINCT ON. See > the "weather reports" example in the SELECT reference page. > > regards, tom lane >
I did try the following: SELECT DISTINCT ON (two.two_id) two.two_value FROM one,two WHERE (one.two_id=two.two_id AND one.updatenr > two.updatenr) ORDER BY two.updatenr ASC; I thought this would a) order the result list by updatenr b) return the first record only for records that are equal on the two.two_id field which would return exactly what I need - return the record with the But what I get instead is "ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions" *grrrmpfh* But after a while playing around I noticed that the "initial" in that sentence above is important - adding two.two_id as first argument to the ORDER BY does not change the result but fulfill the requirements - the expressions don't have to be excactly the same - just the initial argument. My view which returns the (hopefully) correct results: SELECT DISTINCT ON (two.two_id) two.two_value FROM one, two WHERE ((one.two_id = two.two_id) AND (one.updatenr > two.updatenr)) ORDER BY two.two_id, two.updatenr; Next step is to include corresponding information from table three - now I need subselects right? no way around with this DISTINCT and ORDER by stuff in it ... Many thanks for your help so far, Oliver Quoting Oliver Heinz <oheinz@stud.fbi.fh-darmstadt.de>: > I'll try this tomorrow - combining DISTINCT ON (two.two_id) and sorting by > two.updatenr could (should) have the desired effect - I never thought about > using ORDER and DISTINCT that way. > > I'll report my success or failure... > > Thanks so far! > > Bye, > Oliver ------------------------------------------------- This mail sent through IMP: http://horde.org/imp/
Oliver, > SELECT DISTINCT ON (two.two_id) two.two_value FROM one,two WHERE > (one.two_id=two.two_id > AND one.updatenr > two.updatenr) ORDER BY two.updatenr ASC; FWIW, SELECT DISTINCT ON () is slower than SELECT .... ORDER BY ... LIMIT 1 on all stable versions of Postgres. Not that the LIMIT 1 method can be used with all queries. -- -Josh BerkusAglio Database SolutionsSan Francisco