Re: cross-table reference

On Wed, Jun 22, 2005 at 14:56:08 +0530,
Mukesh Ghatiya <mukeshg(at)juniper(dot)net> wrote:
> Hi,
>
> I need to perform a query similar to
>
> SELECT table1.a.b.x table1.a.c.x from table1;
>
>
> In this case "a" is an attribute in 'table1' and is a foreign key to
> other table2 which has 'b', and 'c' as attributes, which again are
> foreign keys to table3.
>
>
> Is there any simple way of doing this other than using the complicated
> nested joins which would include aliases also.
>
> SELECT table31.x table32.x
> FROM table1, table3 AS table31, table3 AS table32
> WHERE table1.a = table2.id
> AND table2.b = table31.id
> AND table2.c = table32.id

This isn't really that complicated. (Though note you left table2 out of the
from item list.)
You might be able to reasonable performance and simplify the select
statement by defining two functions to select from table2 and table3.
If this were written in language SQL the query might even end up with
essentially the same plan.