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

* Self-Referencing
@ 2001-03-28 23:38 David Olbersen <dave@slickness.org>
  2001-03-29 09:27 ` Re: Self-Referencing Christof Glaser <gcg@gl.aser.de>
  2001-03-29 11:47 ` Re: Self-Referencing Cedar Cox <cedarc@visionforisrael.com>
  0 siblings, 2 replies; 3+ messages in thread

From: David Olbersen @ 2001-03-28 23:38 UTC (permalink / raw)
  To: pgsql-sql

Hello,

  I have a feeling this isn't going to make much sense, but I'm gonig to try
  anyway.

  What I'd like to do is be able to refer to an outer-SELECT from an
  inner-SELECT. I hope this makes sense.

  I need to be able to refer to the row that's being processed in a SELECT. I'm
  going to use the idea of 'this' referring to the row that's currently being
  processed. Here's the example of what I'd like:

  SELECT
    building_id,
    num_buildings,
    (
      SELECT count( building_id )
      FROM   building_portals
      WHERE  building_id = THIS.building_id
    )
  FROM buildings;

  Am I making things too complicated, and if so will somebody *PLEASE* tell me
  the easier way to do this. Thanks.

-- Dave




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

* Re: Self-Referencing
  2001-03-28 23:38 Self-Referencing David Olbersen <dave@slickness.org>
@ 2001-03-29 09:27 ` Christof Glaser <gcg@gl.aser.de>
  1 sibling, 0 replies; 3+ messages in thread

From: Christof Glaser @ 2001-03-29 09:27 UTC (permalink / raw)
  To: David Olbersen <dave@slickness.org>; pgsql-sql

On Thursday, 29. March 2001 01:38, David Olbersen wrote:
[snip]
>   SELECT
>     building_id,
>     num_buildings,
>     (
>       SELECT count( building_id )
>       FROM   building_portals
>       WHERE  building_id = THIS.building_id
>     )
>   FROM buildings;

Try this query (untested), using table aliases:

SELECT
  b.building_id, b.num_buildings, 
  ( SELECT count( bp.building_id )
      FROM building_portals bp
     WHERE bp.building_id = b.building_id )
  FROM buildings b;

You could also write <table>.<field> instead.

Hope that helps.

Christof
-- 
          gl.aser . software engineering . internet service
       http://gl.aser.de/  . Planckstraße 7 . D-39104 Magdeburg
Tel. +49.391.7 44 77 10 . Fax +49.391.7 44 77 13 . Mobil 0177.77 92 84 3



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

* Re: Self-Referencing
  2001-03-28 23:38 Self-Referencing David Olbersen <dave@slickness.org>
@ 2001-03-29 11:47 ` Cedar Cox <cedarc@visionforisrael.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Cedar Cox @ 2001-03-29 11:47 UTC (permalink / raw)
  To: David Olbersen <dave@slickness.org>; +Cc: pgsql-sql


On Wed, 28 Mar 2001, David Olbersen wrote:

> Hello,
> 
>   I have a feeling this isn't going to make much sense, but I'm gonig to try
>   anyway.
> 
>   What I'd like to do is be able to refer to an outer-SELECT from an
>   inner-SELECT. I hope this makes sense.
> 
>   I need to be able to refer to the row that's being processed in a SELECT. I'm
>   going to use the idea of 'this' referring to the row that's currently being
>   processed. Here's the example of what I'd like:
> 
>   SELECT
>     building_id,
>     num_buildings,
>     (
>       SELECT count( building_id )
>       FROM   building_portals
>       WHERE  building_id = THIS.building_id
>     )
>   FROM buildings;
> 
>   Am I making things too complicated, and if so will somebody *PLEASE* tell me
>   the easier way to do this. Thanks.
> 
> -- Dave

If I understand what you're trying to do, try this:

  SELECT
    building_id,
    num_buildings,
    (
      SELECT count( building_id )
      FROM   building_portals
      WHERE  building_id = THIS.building_id
    )
  FROM buildings AS THIS;
               __^^^^^^^
                  
You might also want to add in a 'AS num_portals' after the sub-select.

-Cedar




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


end of thread, other threads:[~2001-03-29 11:47 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-28 23:38 Self-Referencing David Olbersen <dave@slickness.org>
2001-03-29 09:27 ` Christof Glaser <gcg@gl.aser.de>
2001-03-29 11:47 ` Cedar Cox <cedarc@visionforisrael.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