Postgres Pro
Downloads
Home   >   mailing lists

Thread: subselects

subselects

From
Joseph Shraibman
Date:
 
I tried to do this:SELECT r , a , (SELECT u , re FROM dir WHERE u = a) , cdate FROM rep
WHERE m IN(190);

... and I got:

ERROR:  Subselect must have only one field

An explain shows that two subselects result in two queries, even thought
they are accessing the same row.  Why can subselects only have one
field?

Database version: PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc
egcs-2.91.66

-- 
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio.  http://www.targabot.com

Re: subselects

From
hubert depesz lubaczewski
Date:
On Wed, Nov 29, 2000 at 07:03:36PM -0500, Joseph Shraibman wrote:
> I tried to do this:
>  SELECT r , a , (SELECT u , re FROM dir WHERE u = a) , cdate FROM rep
> WHERE m IN(190);

why dont you use simple join?
like:
select rep.r, rep.a, rep.cdate, dir.u, dir.re from rep, dir where rep.m in
(190) and rep.a = dir.u;

this should (i guess) work perfectly

depesz

-- 
hubert depesz lubaczewski
------------------------------------------------------------------------    najwspanialszą rzeczą jaką dało nam
nowoczesnespołeczeństwo,     jest niesamowita wręcz łatwość unikania kontaktów z nim ...

Re: subselects

From
Joseph Shraibman
Date:
hubert depesz lubaczewski wrote:
> 
> On Wed, Nov 29, 2000 at 07:03:36PM -0500, Joseph Shraibman wrote:
> > I tried to do this:
> >  SELECT r , a , (SELECT u , re FROM dir WHERE u = a) , cdate FROM rep
> > WHERE m IN(190);
> 
> why dont you use simple join?
> like:
> select rep.r, rep.a, rep.cdate, dir.u, dir.re from rep, dir where rep.m in
> (190) and rep.a = dir.u;
> 
> this should (i guess) work perfectly
> 

Because in my case I'm dynamically generated the select by passing in
some String values, which are inserted into SELECT x,x,x from mytable so
I can't do a simple join.

-- 
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio.  http://www.targabot.com

Re: subselects

From
Carl van Tast
Date:
On Thu, 20 Dec 2001 11:07:59 +0100, "jeewee"
<jeeweeman@engeenspam.hotmail.com> wrote:

>Can anyone help me with this one, I just can't get subselects running
>under pg, like this simple one:
>
>select * from (select name from users) as n;

Works for me with PostgreSQL 7.1.3

Kind regardsCarl van Tast

Re: subselects

From
"Christopher Kings-Lynne"
Date:
> On Thu, 20 Dec 2001 11:07:59 +0100, "jeewee"
> <jeeweeman@engeenspam.hotmail.com> wrote:
>
> >Can anyone help me with this one, I just can't get subselects running
> >under pg, like this simple one:
> >
> >select * from (select name from users) as n;
>
> Works for me with PostgreSQL 7.1.3

Being able to select from, or join to, a SELECT was only introduced in 7.1.
In earlier versions you can use the subselect only(?) in the SELECT
parameter list, and in the where clause.

Chris