Re: Self-Referencing

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