Re: a JOIN on same table, but 'slided over' - Mailing list pgsql-general

Thank you All for this extensive help!

BTW: google helps, once you know that the construct is called
"correlated subquery" - there is no way to get an answer before one
knows the question :)

Thenx again!

-R

On Thu, 2007-06-28 at 23:23 +0530, Gurjeet Singh wrote:
> On 6/28/07, Alban Hertroys <alban@magproductions.nl> wrote:
>
>         This is called a 'correlated subquery'. Basically the subquery
>         is
>         performed for each record in the top query.
>
>         Google gave me this:
>         http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/sqlp/rbafycorrs.htm
>
> I think the sub-section titled "Example: Correlated subquery in a
> WHERE Clause" is appropriate to explain our query at hand.
>
> Simply put, correlated queries are like nested FOR loops of any high
> level programming language.
>
> 1. FOR( record R in result of outer-query )
> 2.   execute inner query, using any R.colname1
> 3.   compare R.colname2 with the result of the correlated-subquery
> 4.   produce R in output, iff the above comparison succeeded
>
> Line 2 can be treated as another FOR loop, where every record of
> inner-query is being processed, and comparing the local expressions
> with a column (or expression) that comes from outer query.
>
> The comparison in step 3 can be against any expression, with columns
> or against a pure constant too!
>
> For example, the following query produces the name of all the
> employees, who manage at least one other employee.
>
> select empno, ename
> from   emp e1
> where  exists (select 1
>                from   emp e2
>                where e2.mgr = e1.empno);
>
> The only thing I would add for our query is that, that the outer
> SELECT of our query produces a cartesian product (no join-condition
> between t1 and t2), but only one row from t2 qualifies for the join,
> since the WHERE condition is on a unique column, and the correlated
> subquery returns just the required value (lowest of the IDs that are
> greater than current t1.ID being processed).
>
> I know the above one-line-paragraph may sound a bit cryptic for
> someone new to correlated subqueries, but if you understand the
> example in the link above, then this would start making some sense.
>
>
>         And there's probably more to find. Interestingly enough
>         wikipedia
>         doesn't seem to have an article on the subject.
>
>
>
>
>
> Regards,
> --
> gurjeet[.singh]@EnterpriseDB.com
> singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
>
> 17°29'34.37"N  78°30'59.76"E - Hyderabad *
> 18°32'57.25"N  73°56'25.42"E - Pune
>
> Sent from my BlackLaptop device