pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProblem using Subselect results
15+ messages / 7 participants
[nested] [flat]
* Problem using Subselect results
@ 2003-07-23 12:51 oheinz@stud.fbi.fh-darmstadt.de
2003-07-28 16:41 ` Re: Problem using Subselect results Bruno Wolff III <bruno@wolff.to>
2003-07-28 16:47 ` Re: Problem using Subselect results Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 15+ messages in thread
From: oheinz@stud.fbi.fh-darmstadt.de @ 2003-07-23 12:51 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Problem using Subselect results
2003-07-23 12:51 Problem using Subselect results oheinz@stud.fbi.fh-darmstadt.de
@ 2003-07-28 16:41 ` Bruno Wolff III <bruno@wolff.to>
1 sibling, 0 replies; 15+ messages in thread
From: Bruno Wolff III @ 2003-07-28 16:41 UTC (permalink / raw)
To: oheinz@stud.fbi.fh-darmstadt.de; +Cc: pgsql-sql
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;
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Problem using Subselect results
2003-07-23 12:51 Problem using Subselect results oheinz@stud.fbi.fh-darmstadt.de
@ 2003-07-28 16:47 ` Josh Berkus <josh@agliodbs.com>
2003-07-29 11:34 ` Re: Problem using Subselect results oheinz@stud.fbi.fh-darmstadt.de
1 sibling, 1 reply; 15+ messages in thread
From: Josh Berkus @ 2003-07-28 16:47 UTC (permalink / raw)
To: oheinz@stud.fbi.fh-darmstadt.de; pgsql-sql
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Problem using Subselect results
2003-07-23 12:51 Problem using Subselect results oheinz@stud.fbi.fh-darmstadt.de
2003-07-28 16:47 ` Re: Problem using Subselect results Josh Berkus <josh@agliodbs.com>
@ 2003-07-29 11:34 ` oheinz@stud.fbi.fh-darmstadt.de
2003-07-29 15:00 ` Re: Problem using Subselect results Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 15+ messages in thread
From: oheinz@stud.fbi.fh-darmstadt.de @ 2003-07-29 11:34 UTC (permalink / raw)
To: pgsql-sql
> 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/
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Problem using Subselect results
2003-07-23 12:51 Problem using Subselect results oheinz@stud.fbi.fh-darmstadt.de
2003-07-28 16:47 ` Re: Problem using Subselect results Josh Berkus <josh@agliodbs.com>
2003-07-29 11:34 ` Re: Problem using Subselect results oheinz@stud.fbi.fh-darmstadt.de
@ 2003-07-29 15:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-07-29 15:14 ` Re: Problem using Subselect results Oliver Heinz <oheinz@stud.fbi.fh-darmstadt.de>
0 siblings, 1 reply; 15+ messages in thread
From: Tom Lane @ 2003-07-29 15:00 UTC (permalink / raw)
To: oheinz@stud.fbi.fh-darmstadt.de; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Problem using Subselect results
2003-07-23 12:51 Problem using Subselect results oheinz@stud.fbi.fh-darmstadt.de
2003-07-28 16:47 ` Re: Problem using Subselect results Josh Berkus <josh@agliodbs.com>
2003-07-29 11:34 ` Re: Problem using Subselect results oheinz@stud.fbi.fh-darmstadt.de
2003-07-29 15:00 ` Re: Problem using Subselect results Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-07-29 15:14 ` Oliver Heinz <oheinz@stud.fbi.fh-darmstadt.de>
2003-07-30 08:52 ` Re: Problem using Subselect results oheinz@stud.fbi.fh-darmstadt.de
0 siblings, 1 reply; 15+ messages in thread
From: Oliver Heinz @ 2003-07-29 15:14 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Problem using Subselect results
2003-07-23 12:51 Problem using Subselect results oheinz@stud.fbi.fh-darmstadt.de
2003-07-28 16:47 ` Re: Problem using Subselect results Josh Berkus <josh@agliodbs.com>
2003-07-29 11:34 ` Re: Problem using Subselect results oheinz@stud.fbi.fh-darmstadt.de
2003-07-29 15:00 ` Re: Problem using Subselect results Tom Lane <tgl@sss.pgh.pa.us>
2003-07-29 15:14 ` Re: Problem using Subselect results Oliver Heinz <oheinz@stud.fbi.fh-darmstadt.de>
@ 2003-07-30 08:52 ` oheinz@stud.fbi.fh-darmstadt.de
2003-07-30 18:08 ` Re: Problem using Subselect results Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 15+ messages in thread
From: oheinz@stud.fbi.fh-darmstadt.de @ 2003-07-30 08:52 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Problem using Subselect results
2003-07-23 12:51 Problem using Subselect results oheinz@stud.fbi.fh-darmstadt.de
2003-07-28 16:47 ` Re: Problem using Subselect results Josh Berkus <josh@agliodbs.com>
2003-07-29 11:34 ` Re: Problem using Subselect results oheinz@stud.fbi.fh-darmstadt.de
2003-07-29 15:00 ` Re: Problem using Subselect results Tom Lane <tgl@sss.pgh.pa.us>
2003-07-29 15:14 ` Re: Problem using Subselect results Oliver Heinz <oheinz@stud.fbi.fh-darmstadt.de>
2003-07-30 08:52 ` Re: Problem using Subselect results oheinz@stud.fbi.fh-darmstadt.de
@ 2003-07-30 18:08 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 15+ messages in thread
From: Josh Berkus @ 2003-07-30 18:08 UTC (permalink / raw)
To: oheinz@stud.fbi.fh-darmstadt.de; pgsql-sql
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 Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 15+ messages in thread
* Problem using Subselect results
@ 2003-07-24 08:39 oheinz@stud.fbi.fh-darmstadt.de
0 siblings, 0 replies; 15+ messages in thread
From: oheinz@stud.fbi.fh-darmstadt.de @ 2003-07-24 08:39 UTC (permalink / raw)
To: pgsql-sql
Hi all,
I want to use the result of a subselect as condition in another one.
table1: a,b
table2: a,c
CREATE VIEW my_view AS SELECT b,c
(SELECT a, b FROM table1 WHERE b=1) my_ab,
(SELECT c FROM table2, my_ab WHERE table3.a=my_ab.a) my_c;
this is just an example - i know i could cross join this one, but i need to
refer to the results of several subselects in several other.
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?
And 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/
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Problem using Subselect results
@ 2003-07-24 12:11 Christoph Haller <ch@rodos.fzk.de>
2003-07-24 13:01 ` Re: Problem using Subselect results oheinz@stud.fbi.fh-darmstadt.de
0 siblings, 1 reply; 15+ messages in thread
From: Christoph Haller @ 2003-07-24 12:11 UTC (permalink / raw)
To: oheinz@stud.fbi.fh-darmstadt.de; pgsql-sql
> I want to use the result of a subselect as condition in another one.
>
> table1: a,b
> table2: a,c
>
> CREATE VIEW my_view AS SELECT b,c
> (SELECT a, b FROM table1 WHERE b=3D1) my_ab,
> (SELECT c FROM table2, my_ab WHERE table3.a=3Dmy_ab.a) my_c;
>
> this is just an example - i know i could cross join this one, but i
need =
> to=20
> refer to the results of several subselects in several other.
>
>
> does return "relation my_ab unknown". it is not just a problem of
executi=
> on=20
> 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
(nece=
> ssary)=20
> AS statement for subselects, if it's not possible to access their
results=
> by=20
> that name?
>
> And as I need the result of a subselect in several other subselects
it's=
> not=20
> possible to transform them into a cascade of sub, subsub,
subsubsub.... s=
> elects.
> Any ideas?=20
>
Does this match your intentions:
CREATE VIEW my_view AS SELECT b,c FROM
(SELECT b,c FROM table2, (SELECT a, b FROM table1 WHERE b=3D1) my_ab
WHERE table3.a=3Dmy_ab.a) my_c;
I assume the reference table3.a is a typo.
Regards, Christoph
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Problem using Subselect results
2003-07-24 12:11 Re: Problem using Subselect results Christoph Haller <ch@rodos.fzk.de>
@ 2003-07-24 13:01 ` oheinz@stud.fbi.fh-darmstadt.de
2003-07-24 14:54 ` Re: Problem using Subselect results Dmitry Tkach <dmitry@openratings.com>
0 siblings, 1 reply; 15+ messages in thread
From: oheinz@stud.fbi.fh-darmstadt.de @ 2003-07-24 13:01 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; +Cc: pgsql-sql
SELECT my_c.b, my_c.c FROM (SELECT table2.b, table2.c FROM table2, (SELECT
table1.a, table1.b FROM table1 WHERE (table1.b = 1)) my_ab WHERE (table2.a =
my_ab.a)) my_c;
You were doing what I wanted to avoid - you are using a "SUBSUBSELECT".
But (now) I believe it's not possible to refer to a subselect's resultset on
the same level of hierarchy - which sounds rather meaningful - because you
couldn't tell which of them was being processsed first.
So I'll have to get my SELECT statement into some kind of hierarchy, which
makes things a bit more complicated (with twentysomething SELECT statements)
Thanks,
Oliver
Quoting Christoph Haller <ch@rodos.fzk.de>:
> Does this match your intentions:
> CREATE VIEW my_view AS SELECT b,c FROM
> (SELECT b,c FROM table2, (SELECT a, b FROM table1 WHERE b=3D1) my_ab
> WHERE table3.a=3Dmy_ab.a) my_c;
> I assume the reference table3.a is a typo.
>
> Regards, Christoph
>
> > I want to use the result of a subselect as condition in another one.
> >
> > table1: a,b
> > table2: a,c
> >
> > CREATE VIEW my_view AS SELECT b,c
> > (SELECT a, b FROM table1 WHERE b=3D1) my_ab,
> > (SELECT c FROM table2, my_ab WHERE table3.a=3Dmy_ab.a) my_c;
> >
> > this is just an example - i know i could cross join this one, but i
> need =
> > to=20
> > refer to the results of several subselects in several other.
> >
> >
> > does return "relation my_ab unknown". it is not just a problem of
> executi=
> > on=20
> > 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
> (nece=
> > ssary)=20
> > AS statement for subselects, if it's not possible to access their
> results=
> > by=20
> > that name?
> >
> > And as I need the result of a subselect in several other subselects
> it's=
> > not=20
> > possible to transform them into a cascade of sub, subsub,
> subsubsub.... s=
> > elects.
> > Any ideas?=20
> >
-------------------------------------------------
This mail sent through IMP: http://horde.org/imp/
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Problem using Subselect results
2003-07-24 12:11 Re: Problem using Subselect results Christoph Haller <ch@rodos.fzk.de>
2003-07-24 13:01 ` Re: Problem using Subselect results oheinz@stud.fbi.fh-darmstadt.de
@ 2003-07-24 14:54 ` Dmitry Tkach <dmitry@openratings.com>
2003-07-25 09:01 ` Re: Problem using Subselect results oheinz@stud.fbi.fh-darmstadt.de
0 siblings, 1 reply; 15+ messages in thread
From: Dmitry Tkach @ 2003-07-24 14:54 UTC (permalink / raw)
To: oheinz@stud.fbi.fh-darmstadt.de; +Cc: Christoph Haller <ch@rodos.fzk.de>; pgsql-sql
oheinz@stud.fbi.fh-darmstadt.de wrote:
>SELECT my_c.b, my_c.c FROM (SELECT table2.b, table2.c FROM table2, (SELECT
>table1.a, table1.b FROM table1 WHERE (table1.b = 1)) my_ab WHERE (table2.a =
>my_ab.a)) my_c;
>
>You were doing what I wanted to avoid - you are using a "SUBSUBSELECT".
>
>
What about:
CREATE VIEW my_view AS SELECT b,c from
(SELECT a, b FROM table1 WHERE b=1) as my_ab,
(SELECT a, c FROM table2) as my_ac WHERE my_ac.a=my_ab.a
This looks like what you are trying to do, and doesn't use that 'subsubselect' you were trying to avoid...
BTW, what is special to the second-level subselect, compared to the first level one? Why are you trying to avoid one, but not the other?
I mean, I could understand, if you (like me) just hated subselects alltogether (then you would have converted your query into a join), but it looks like you don't...
Dima
>But (now) I believe it's not possible to refer to a subselect's resultset on
>the same level of hierarchy - which sounds rather meaningful - because you
>couldn't tell which of them was being processsed first.
>
>So I'll have to get my SELECT statement into some kind of hierarchy, which
>makes things a bit more complicated (with twentysomething SELECT statements)
>
>Thanks,
>Oliver
>
>
>Quoting Christoph Haller <ch@rodos.fzk.de>:
>
>
>>Does this match your intentions:
>> CREATE VIEW my_view AS SELECT b,c FROM
>> (SELECT b,c FROM table2, (SELECT a, b FROM table1 WHERE b=3D1) my_ab
>>WHERE table3.a=3Dmy_ab.a) my_c;
>>I assume the reference table3.a is a typo.
>>
>>Regards, Christoph
>>
>>
>>
>
>
>
>>>I want to use the result of a subselect as condition in another one.
>>>
>>>table1: a,b
>>>table2: a,c
>>>
>>>CREATE VIEW my_view AS SELECT b,c
>>>(SELECT a, b FROM table1 WHERE b=3D1) my_ab,
>>>(SELECT c FROM table2, my_ab WHERE table3.a=3Dmy_ab.a) my_c;
>>>
>>>this is just an example - i know i could cross join this one, but i
>>>
>>>
>>need =
>>
>>
>>>to=20
>>>refer to the results of several subselects in several other.
>>>
>>>
>>>does return "relation my_ab unknown". it is not just a problem of
>>>
>>>
>>executi=
>>
>>
>>>on=20
>>>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
>>>
>>>
>>(nece=
>>
>>
>>>ssary)=20
>>>AS statement for subselects, if it's not possible to access their
>>>
>>>
>>results=
>>
>>
>>> by=20
>>>that name?
>>>
>>>And as I need the result of a subselect in several other subselects
>>>
>>>
>>it's=
>>
>>
>>> not=20
>>>possible to transform them into a cascade of sub, subsub,
>>>
>>>
>>subsubsub.... s=
>>
>>
>>>elects.
>>>Any ideas?=20
>>>
>>>
>>>
>
>
>
>-------------------------------------------------
>This mail sent through IMP: http://horde.org/imp/
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Problem using Subselect results
2003-07-24 12:11 Re: Problem using Subselect results Christoph Haller <ch@rodos.fzk.de>
2003-07-24 13:01 ` Re: Problem using Subselect results oheinz@stud.fbi.fh-darmstadt.de
2003-07-24 14:54 ` Re: Problem using Subselect results Dmitry Tkach <dmitry@openratings.com>
@ 2003-07-25 09:01 ` oheinz@stud.fbi.fh-darmstadt.de
2003-07-25 13:45 ` Re: Problem using Subselect results Tom Lane <tgl@sss.pgh.pa.us>
2003-07-25 14:23 ` Re: Problem using Subselect results Dmitry Tkach <dmitry@openratings.com>
0 siblings, 2 replies; 15+ messages in thread
From: oheinz@stud.fbi.fh-darmstadt.de @ 2003-07-25 09:01 UTC (permalink / raw)
To: Dmitry Tkach <dmitry@openratings.com>; +Cc: Christoph Haller <ch@rodos.fzk.de>; pgsql-sql
Quoting Dmitry Tkach <dmitry@openratings.com>:
> What about:
>
> CREATE VIEW my_view AS SELECT b,c from
> (SELECT a, b FROM table1 WHERE b=1) as my_ab,
> (SELECT a, c FROM table2) as my_ac WHERE my_ac.a=my_ab.a
>
> This looks like what you are trying to do, and doesn't use that
> 'subsubselect' you were trying to avoid...
I assume that with this statement postgresql will compute both subselects, do
a cross join on both results an then reduce them to those who match the
condition my_ac.a=my_ab.a, right?
What I was trying to do is reduce the results to a minimum before joining them.
It's not only two or three tables and some of them will grow big, so joining
them first and reducing them later may not be such a good idea.
My first try (which does not work, because I'm trying to access results of
subselects on the same hierarchy level):
CREATE VIEW public.aufwaende_test AS SELECT aufwaende.aufwandsid,
aufwaende.auftragsid, aufwaende.taetigkeitsid, my_taetigkeiten.taetigkeit,
aufwaende.mitarbeiterid, my_mitarbeiter.vorname, my_mitarbeiter.nachname,
my_mitarbeiter.pknummer, aufwaende.dauer, aufwaende.datum,
my_auftraege.aufgabenid, my_aufgaben.aufgabe, my_auftraege.systemid,
my_systeme.system, my_systeme.kundenid, my_kunden.kunde, aufwaende.status FROM
aufwaende,
(SELECT auftraege_complete.systemid, auftraege_complete.aufgabenid,
auftraege_complete.updatenr FROM auftraege_complete WHERE
(auftraege_complete.updatenr = (SELECT max(auftraege_complete.updatenr) AS max
FROM auftraege_complete WHERE ((auftraege_complete.auftragsid =
aufwaende.auftragsid))))) my_auftraege,
(SELECT aufgaben_complete.bezeichnung AS aufgabe FROM aufgaben_complete WHERE
(aufgaben_complete.updatenr = (SELECT max(aufgaben_complete.updatenr) AS max
FROM aufgaben_complete WHERE ((aufgaben_complete.aufgabenid =
auftraege_complete.aufgabenid) AND (aufgaben_complete.updatenr <
my_auftraege.updatenr))))) my_aufgaben,
(SELECT taetigkeiten_complete.bezeichnung AS taetigkeit FROM
taetigkeiten_complete WHERE (taetigkeiten_complete.updatenr = (SELECT max
(taetigkeiten_complete.updatenr) AS max FROM taetigkeiten_complete WHERE
((taetigkeiten_complete.taetigkeitsid = aufwaende.taetigkeitsid) AND
(taetigkeiten_complete.updatenr < my_auftraege.updatenr))))) my_taetigkeiten,
(SELECT systeme_complete.name AS system, systeme_complete.kundenid,
systeme_complete.systemid AS sys_sysid FROM systeme_complete WHERE
(systeme_complete.updatenr = (SELECT max(systeme_complete.updatenr) AS max FROM
systeme_complete WHERE ((systeme_complete.systemid =
auftraege_complete.systemid) AND (systeme_complete.updatenr <
my_auftraege.updatenr))))) my_systeme,
(SELECT kunden_complete.name AS kunde FROM kunden_complete WHERE
(kunden_complete.updatenr = (SELECT max(kunden_complete.updatenr) AS max FROM
kunden_complete WHERE ((kunden_complete.kundenid = my_systeme.kundenid) AND
(kunden_complete.updatenr < aufwaende.updatenr))))) my_kunden,
(SELECT mitarbeiter_complete.nachname, mitarbeiter_complete.vorname,
mitarbeiter_complete.pknummer FROM mitarbeiter_complete WHERE
(mitarbeiter_complete.updatenr = (SELECT max(mitarbeiter_complete.updatenr) AS
max FROM mitarbeiter_complete WHERE ((mitarbeiter_complete.mitarbeiterid =
aufwaende.mitarbeiterid) AND (mitarbeiter_complete.updatenr <
my_auftraege.updatenr))))) my_mitarbeiter;
as you can see most of them use my_auftraege.updatenr as one condition, and the
subselect on kunden_complete uses results from the my_systeme subselect
(my_systeme.kundenid)
Now I see two possibilities
- join the early and reduce them later
- create a hierarchy so that (sub...)selects which rely on the result of
another select include this select-statement as a (sub...)subselect.
Any better Ideas?
TIA,
Oliver
>
> BTW, what is special to the second-level subselect, compared to the first
> level one? Why are you trying to avoid one, but not the other?
>
> I mean, I could understand, if you (like me) just hated subselects
> alltogether (then you would have converted your query into a join), but it
> looks like you don't...
>
> Dima
>
>
>
>
> >But (now) I believe it's not possible to refer to a subselect's resultset on
>
> >the same level of hierarchy - which sounds rather meaningful - because you
>
> >couldn't tell which of them was being processsed first.
> >
> >So I'll have to get my SELECT statement into some kind of hierarchy, which
>
> >makes things a bit more complicated (with twentysomething SELECT
> statements)
> >
> >Thanks,
> >Oliver
> >
> >
> >Quoting Christoph Haller <ch@rodos.fzk.de>:
> >
> >
> >>Does this match your intentions:
> >> CREATE VIEW my_view AS SELECT b,c FROM
> >> (SELECT b,c FROM table2, (SELECT a, b FROM table1 WHERE b=3D1) my_ab
> >>WHERE table3.a=3Dmy_ab.a) my_c;
> >>I assume the reference table3.a is a typo.
> >>
> >>Regards, Christoph
> >>
> >>
> >>
> >
> >
> >
> >>>I want to use the result of a subselect as condition in another one.
> >>>
> >>>table1: a,b
> >>>table2: a,c
> >>>
> >>>CREATE VIEW my_view AS SELECT b,c
> >>>(SELECT a, b FROM table1 WHERE b=3D1) my_ab,
> >>>(SELECT c FROM table2, my_ab WHERE table3.a=3Dmy_ab.a) my_c;
> >>>
> >>>this is just an example - i know i could cross join this one, but i
> >>>
> >>>
> >>need =
> >>
> >>
> >>>to=20
> >>>refer to the results of several subselects in several other.
> >>>
> >>>
> >>>does return "relation my_ab unknown". it is not just a problem of
> >>>
> >>>
> >>executi=
> >>
> >>
> >>>on=20
> >>>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
> >>>
> >>>
> >>(nece=
> >>
> >>
> >>>ssary)=20
> >>>AS statement for subselects, if it's not possible to access their
> >>>
> >>>
> >>results=
> >>
> >>
> >>> by=20
> >>>that name?
> >>>
> >>>And as I need the result of a subselect in several other subselects
> >>>
> >>>
> >>it's=
> >>
> >>
> >>> not=20
> >>>possible to transform them into a cascade of sub, subsub,
> >>>
> >>>
> >>subsubsub.... s=
> >>
> >>
> >>>elects.
> >>>Any ideas?=20
> >>>
> >>>
> >>>
> >
> >
> >
> >-------------------------------------------------
> >This mail sent through IMP: http://horde.org/imp/
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> >
> >
>
>
-------------------------------------------------
This mail sent through IMP: http://horde.org/imp/
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Problem using Subselect results
2003-07-24 12:11 Re: Problem using Subselect results Christoph Haller <ch@rodos.fzk.de>
2003-07-24 13:01 ` Re: Problem using Subselect results oheinz@stud.fbi.fh-darmstadt.de
2003-07-24 14:54 ` Re: Problem using Subselect results Dmitry Tkach <dmitry@openratings.com>
2003-07-25 09:01 ` Re: Problem using Subselect results oheinz@stud.fbi.fh-darmstadt.de
@ 2003-07-25 13:45 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 15+ messages in thread
From: Tom Lane @ 2003-07-25 13:45 UTC (permalink / raw)
To: oheinz@stud.fbi.fh-darmstadt.de; +Cc: Dmitry Tkach <dmitry@openratings.com>; Christoph Haller <ch@rodos.fzk.de>; pgsql-sql
oheinz@stud.fbi.fh-darmstadt.de writes:
> Quoting Dmitry Tkach <dmitry@openratings.com>:
>> CREATE VIEW my_view AS SELECT b,c from
>> (SELECT a, b FROM table1 WHERE b=1) as my_ab,
>> (SELECT a, c FROM table2) as my_ac WHERE my_ac.a=my_ab.a
> I assume that with this statement postgresql will compute both subselects, do
> a cross join on both results an then reduce them to those who match the
> condition my_ac.a=my_ab.a, right?
No, it's smarter than that.
I tried the experiment in 7.3 and CVS tip, using some tables from the
regression database:
regression=# create view my_view as select b,c from
regression-# (select unique1,unique2 from tenk1 where unique2=1) as
regression-# my_ab(a,b),
regression-# (select unique1,unique2 from onek) as my_ac(a,c)
regression-# where my_ac.a = my_ab.a;
CREATE VIEW
regression=# explain select * from my_view;
QUERY PLAN
---------------------------------------------------------------------------------
Nested Loop (cost=0.00..24.47 rows=1 width=16)
-> Index Scan using tenk1_unique2 on tenk1 (cost=0.00..3.01 rows=1 width=8)
Index Cond: (unique2 = 1)
-> Index Scan using onek_unique1 on onek (cost=0.00..21.40 rows=5 width=8)
Index Cond: (onek.unique1 = "outer".unique1)
(5 rows)
regression=#
Looks like a fairly decent plan to me. It's certainly not letting the
sub-select structure get in its way.
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Problem using Subselect results
2003-07-24 12:11 Re: Problem using Subselect results Christoph Haller <ch@rodos.fzk.de>
2003-07-24 13:01 ` Re: Problem using Subselect results oheinz@stud.fbi.fh-darmstadt.de
2003-07-24 14:54 ` Re: Problem using Subselect results Dmitry Tkach <dmitry@openratings.com>
2003-07-25 09:01 ` Re: Problem using Subselect results oheinz@stud.fbi.fh-darmstadt.de
@ 2003-07-25 14:23 ` Dmitry Tkach <dmitry@openratings.com>
1 sibling, 0 replies; 15+ messages in thread
From: Dmitry Tkach @ 2003-07-25 14:23 UTC (permalink / raw)
To: oheinz@stud.fbi.fh-darmstadt.de; +Cc: Christoph Haller <ch@rodos.fzk.de>; pgsql-sql
oheinz@stud.fbi.fh-darmstadt.de wrote:
>Quoting Dmitry Tkach <dmitry@openratings.com>:
>
>
>>What about:
>>
>>CREATE VIEW my_view AS SELECT b,c from
>>(SELECT a, b FROM table1 WHERE b=1) as my_ab,
>>(SELECT a, c FROM table2) as my_ac WHERE my_ac.a=my_ab.a
>>
>>This looks like what you are trying to do, and doesn't use that
>>'subsubselect' you were trying to avoid...
>>
>>
>
>I assume that with this statement postgresql will compute both subselects, do
>a cross join on both results an then reduce them to those who match the
>condition my_ac.a=my_ab.a, right?
>
>
I don't think so... Not totally sure, but I believe, that, at least in
this case, the query plan will be equivalent to a join...
>What I was trying to do is reduce the results to a minimum before joining them.
>It's not only two or three tables and some of them will grow big, so joining
>them first and reducing them later may not be such a good idea.
>
I am not sure I understand what you mean by 'reducing'.
It seems to me that you could make your query a lot simpler by
converting it into a join, and I don't see anything you are buying by
those subselects....
Also, you may want to get rid of max(), and replace those things with
'select column from table order by column desc limit 1'. This should be
a lot quicker (provided that you have an index on that column).
Dima
>
>
>My first try (which does not work, because I'm trying to access results of
>subselects on the same hierarchy level):
>
>
>
>CREATE VIEW public.aufwaende_test AS SELECT aufwaende.aufwandsid,
>aufwaende.auftragsid, aufwaende.taetigkeitsid, my_taetigkeiten.taetigkeit,
>aufwaende.mitarbeiterid, my_mitarbeiter.vorname, my_mitarbeiter.nachname,
>my_mitarbeiter.pknummer, aufwaende.dauer, aufwaende.datum,
>my_auftraege.aufgabenid, my_aufgaben.aufgabe, my_auftraege.systemid,
>my_systeme.system, my_systeme.kundenid, my_kunden.kunde, aufwaende.status FROM
>aufwaende,
>
>(SELECT auftraege_complete.systemid, auftraege_complete.aufgabenid,
>auftraege_complete.updatenr FROM auftraege_complete WHERE
>(auftraege_complete.updatenr = (SELECT max(auftraege_complete.updatenr) AS max
>FROM auftraege_complete WHERE ((auftraege_complete.auftragsid =
>aufwaende.auftragsid))))) my_auftraege,
>
>(SELECT aufgaben_complete.bezeichnung AS aufgabe FROM aufgaben_complete WHERE
>(aufgaben_complete.updatenr = (SELECT max(aufgaben_complete.updatenr) AS max
>FROM aufgaben_complete WHERE ((aufgaben_complete.aufgabenid =
>auftraege_complete.aufgabenid) AND (aufgaben_complete.updatenr <
>my_auftraege.updatenr))))) my_aufgaben,
>
>(SELECT taetigkeiten_complete.bezeichnung AS taetigkeit FROM
>taetigkeiten_complete WHERE (taetigkeiten_complete.updatenr = (SELECT max
>(taetigkeiten_complete.updatenr) AS max FROM taetigkeiten_complete WHERE
>((taetigkeiten_complete.taetigkeitsid = aufwaende.taetigkeitsid) AND
>(taetigkeiten_complete.updatenr < my_auftraege.updatenr))))) my_taetigkeiten,
>
>(SELECT systeme_complete.name AS system, systeme_complete.kundenid,
>systeme_complete.systemid AS sys_sysid FROM systeme_complete WHERE
>(systeme_complete.updatenr = (SELECT max(systeme_complete.updatenr) AS max FROM
>systeme_complete WHERE ((systeme_complete.systemid =
>auftraege_complete.systemid) AND (systeme_complete.updatenr <
>my_auftraege.updatenr))))) my_systeme,
>
>(SELECT kunden_complete.name AS kunde FROM kunden_complete WHERE
>(kunden_complete.updatenr = (SELECT max(kunden_complete.updatenr) AS max FROM
>kunden_complete WHERE ((kunden_complete.kundenid = my_systeme.kundenid) AND
>(kunden_complete.updatenr < aufwaende.updatenr))))) my_kunden,
>
>(SELECT mitarbeiter_complete.nachname, mitarbeiter_complete.vorname,
>mitarbeiter_complete.pknummer FROM mitarbeiter_complete WHERE
>(mitarbeiter_complete.updatenr = (SELECT max(mitarbeiter_complete.updatenr) AS
>max FROM mitarbeiter_complete WHERE ((mitarbeiter_complete.mitarbeiterid =
>aufwaende.mitarbeiterid) AND (mitarbeiter_complete.updatenr <
>my_auftraege.updatenr))))) my_mitarbeiter;
>
>
>as you can see most of them use my_auftraege.updatenr as one condition, and the
>subselect on kunden_complete uses results from the my_systeme subselect
>(my_systeme.kundenid)
>
>
>
>Now I see two possibilities
>
>- join the early and reduce them later
>- create a hierarchy so that (sub...)selects which rely on the result of
>another select include this select-statement as a (sub...)subselect.
>
>
>Any better Ideas?
>
>TIA,
>Oliver
>
>
>
>
>>BTW, what is special to the second-level subselect, compared to the first
>>level one? Why are you trying to avoid one, but not the other?
>>
>>I mean, I could understand, if you (like me) just hated subselects
>>alltogether (then you would have converted your query into a join), but it
>>looks like you don't...
>>
>>Dima
>>
>>
>>
>>
>>
>>
>>>But (now) I believe it's not possible to refer to a subselect's resultset on
>>>
>>>
>>>the same level of hierarchy - which sounds rather meaningful - because you
>>>
>>>
>>>couldn't tell which of them was being processsed first.
>>>
>>>So I'll have to get my SELECT statement into some kind of hierarchy, which
>>>
>>>
>>>makes things a bit more complicated (with twentysomething SELECT
>>>
>>>
>>statements)
>>
>>
>>>Thanks,
>>>Oliver
>>>
>>>
>>>Quoting Christoph Haller <ch@rodos.fzk.de>:
>>>
>>>
>>>
>>>
>>>>Does this match your intentions:
>>>>CREATE VIEW my_view AS SELECT b,c FROM
>>>>(SELECT b,c FROM table2, (SELECT a, b FROM table1 WHERE b=3D1) my_ab
>>>>WHERE table3.a=3Dmy_ab.a) my_c;
>>>>I assume the reference table3.a is a typo.
>>>>
>>>>Regards, Christoph
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>>
>>>>>I want to use the result of a subselect as condition in another one.
>>>>>
>>>>>table1: a,b
>>>>>table2: a,c
>>>>>
>>>>>CREATE VIEW my_view AS SELECT b,c
>>>>>(SELECT a, b FROM table1 WHERE b=3D1) my_ab,
>>>>>(SELECT c FROM table2, my_ab WHERE table3.a=3Dmy_ab.a) my_c;
>>>>>
>>>>>this is just an example - i know i could cross join this one, but i
>>>>>
>>>>>
>>>>>
>>>>>
>>>>need =
>>>>
>>>>
>>>>
>>>>
>>>>>to=20
>>>>>refer to the results of several subselects in several other.
>>>>>
>>>>>
>>>>>does return "relation my_ab unknown". it is not just a problem of
>>>>>
>>>>>
>>>>>
>>>>>
>>>>executi=
>>>>
>>>>
>>>>
>>>>
>>>>>on=20
>>>>>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
>>>>>
>>>>>
>>>>>
>>>>>
>>>>(nece=
>>>>
>>>>
>>>>
>>>>
>>>>>ssary)=20
>>>>>AS statement for subselects, if it's not possible to access their
>>>>>
>>>>>
>>>>>
>>>>>
>>>>results=
>>>>
>>>>
>>>>
>>>>
>>>>>by=20
>>>>>that name?
>>>>>
>>>>>And as I need the result of a subselect in several other subselects
>>>>>
>>>>>
>>>>>
>>>>>
>>>>it's=
>>>>
>>>>
>>>>
>>>>
>>>>>not=20
>>>>>possible to transform them into a cascade of sub, subsub,
>>>>>
>>>>>
>>>>>
>>>>>
>>>>subsubsub.... s=
>>>>
>>>>
>>>>
>>>>
>>>>>elects.
>>>>>Any ideas?=20
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>
>>>-------------------------------------------------
>>>This mail sent through IMP: http://horde.org/imp/
>>>
>>>---------------------------(end of broadcast)---------------------------
>>>TIP 3: if posting/reading through Usenet, please send an appropriate
>>> subscribe-nomail command to majordomo@postgresql.org so that your
>>> message can get through to the mailing list cleanly
>>>
>>>
>>>
>>>
>>
>>
>
>
>
>
>-------------------------------------------------
>This mail sent through IMP: http://horde.org/imp/
>
>
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2003-07-30 18:08 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-23 12:51 Problem using Subselect results oheinz@stud.fbi.fh-darmstadt.de
2003-07-28 16:41 ` Bruno Wolff III <bruno@wolff.to>
2003-07-28 16:47 ` Josh Berkus <josh@agliodbs.com>
2003-07-29 11:34 ` oheinz@stud.fbi.fh-darmstadt.de
2003-07-29 15:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-07-29 15:14 ` Oliver Heinz <oheinz@stud.fbi.fh-darmstadt.de>
2003-07-30 08:52 ` oheinz@stud.fbi.fh-darmstadt.de
2003-07-30 18:08 ` Josh Berkus <josh@agliodbs.com>
2003-07-24 08:39 Problem using Subselect results oheinz@stud.fbi.fh-darmstadt.de
2003-07-24 12:11 Re: Problem using Subselect results Christoph Haller <ch@rodos.fzk.de>
2003-07-24 13:01 ` oheinz@stud.fbi.fh-darmstadt.de
2003-07-24 14:54 ` Dmitry Tkach <dmitry@openratings.com>
2003-07-25 09:01 ` oheinz@stud.fbi.fh-darmstadt.de
2003-07-25 13:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-07-25 14:23 ` Dmitry Tkach <dmitry@openratings.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox