Thread: Self-Referencing

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'
referringto 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

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

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