Re: cross-table reference - Mailing list pgsql-sql

Please keep replies copied to the list unless you have a specific reason
not to. This lets more people potentially help you and lets others
learn from the discussion.

On Wed, Jun 29, 2005 at 14:09:16 +0530, Mukesh Ghatiya <mukeshg@juniper.net> wrote:
> Hi Bruno,
> 
> Thanks for the response. Ok, let me clarify my problem further.
> My actual problem is to convert a simplistic query of form  "SELECT
> table1.a.b.x table1.a.c.x from table1;" to its working SQL equivalent.
> The input query can be further complex in terms of depth of the columns,
> number of columns, and such multi-level-columns can be present in
> 'where' clause as well.
> 
> Now, I hope you realize it will be a quite tricky job to write a generic
> algorithm to convert such a query to its sql equivalent. So I was just
> wondering if the output can be simplified, that will make the algo less
> tricky.

I don't think having simpler output is needed for this. The custom
query generator needs to understand what you are telling it. Once it
does it should be able to produce the required output.

> Also, I was just wondering why doesn't DB support such queries? It looks
> so intuitive (and small) to write such queries.
> Ideally DB should be able to do this, because it knows that 'a' is an
> attribute in 'table1' which is a foreign key to 'table2', and so on.

Talk to the SQL Standards committee. The syntax you are suggesting is going
to conflict with existing meaning.

> Please let me know if I didn't make myself clear.
> 
> Thanks,
> Mukesh
> 
> -----Original Message-----
> From: Bruno Wolff III [mailto:bruno@wolff.to] 
> Sent: Tuesday, June 28, 2005 6:18 PM
> To: Mukesh Ghatiya
> Cc: pgsql-sql@postgresql.org
> Subject: Re: cross-table reference
> 
> On Wed, Jun 22, 2005 at 14:56:08 +0530,
>   Mukesh Ghatiya <mukeshg@juniper.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.