cross-table reference

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

cross-table reference

Mukesh Ghatiya
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


Thanks,
Mukesh

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|

Re: cross-table reference

Bruno Wolff III
On Wed, Jun 22, 2005 at 14:56:08 +0530,
  Mukesh Ghatiya <[hidden email]> 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.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
Reply | Threaded
Open this post in threaded view
|

Re: cross-table reference

Bruno Wolff III
In reply to this post by Mukesh Ghatiya
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 <[hidden email]> 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:[hidden email]]
> Sent: Tuesday, June 28, 2005 6:18 PM
> To: Mukesh Ghatiya
> Cc: [hidden email]
> Subject: Re: cross-table reference
>
> On Wed, Jun 22, 2005 at 14:56:08 +0530,
>   Mukesh Ghatiya <[hidden email]> 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.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly