pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
subselects
5+ messages / 4 participants
[nested] [flat]

* subselects
@ 2000-11-30 00:03 Joseph Shraibman <jks@selectacast.net>
  2000-12-02 10:27 ` Re: subselects hubert depesz lubaczewski <depesz@depesz.pl>
  0 siblings, 1 reply; 5+ messages in thread

From: Joseph Shraibman @ 2000-11-30 00:03 UTC (permalink / raw)
  To: pgsql-sql

 
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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: subselects
  2000-11-30 00:03 subselects Joseph Shraibman <jks@selectacast.net>
@ 2000-12-02 10:27 ` hubert depesz lubaczewski <depesz@depesz.pl>
  2000-12-06 20:23   ` Re: subselects Joseph Shraibman <jks@selectacast.net>
  0 siblings, 1 reply; 5+ messages in thread

From: hubert depesz lubaczewski @ 2000-12-02 10:27 UTC (permalink / raw)
  To: pgsql-sql

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 nowoczesne społeczeństwo,
      jest niesamowita wręcz łatwość unikania kontaktów z nim ...



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: subselects
  2000-11-30 00:03 subselects Joseph Shraibman <jks@selectacast.net>
  2000-12-02 10:27 ` Re: subselects hubert depesz lubaczewski <depesz@depesz.pl>
@ 2000-12-06 20:23   ` Joseph Shraibman <jks@selectacast.net>
  0 siblings, 0 replies; 5+ messages in thread

From: Joseph Shraibman @ 2000-12-06 20:23 UTC (permalink / raw)
  To: depesz@depesz.pl; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: subselects
@ 2001-12-20 13:50 Carl van Tast <vanTast@Pivot.at>
  2001-12-21 02:45 ` Re: subselects Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  0 siblings, 1 reply; 5+ messages in thread

From: Carl van Tast @ 2001-12-20 13:50 UTC (permalink / raw)
  To: pgsql-sql

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 regards
 Carl van Tast



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: subselects
  2001-12-20 13:50 Re: subselects Carl van Tast <vanTast@Pivot.at>
@ 2001-12-21 02:45 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  0 siblings, 0 replies; 5+ messages in thread

From: Christopher Kings-Lynne @ 2001-12-21 02:45 UTC (permalink / raw)
  To: Carl van Tast <vanTast@Pivot.at>; pgsql-sql

> 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




^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2001-12-21 02:45 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-11-30 00:03 subselects Joseph Shraibman <jks@selectacast.net>
2000-12-02 10:27 ` hubert depesz lubaczewski <depesz@depesz.pl>
2000-12-06 20:23   ` Joseph Shraibman <jks@selectacast.net>
2001-12-20 13:50 Re: subselects Carl van Tast <vanTast@Pivot.at>
2001-12-21 02:45 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>

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