Re: [gnome-db] GdaSqlBuiler: JOIN AS

[Murray Cumming <murrayc murrayc com>]

On Mon, 2010-05-17 at 14:43 +0200, Vivien Malerba wrote:
> On 17 May 2010 14:39, Murray Cumming <murrayc murrayc com> wrote:
> > On Mon, 2010-05-17 at 14:20 +0200, Vivien Malerba wrote:
> >> On 17 May 2010 10:07, Murray Cumming <murrayc murrayc com> wrote:
> >> > In Glom, I use the AS keyword with JOINS, to allow multiple joins with
> >> > the same table. A simple case looks like this, though it's unnecessary
> >> > in this particular example:
> >> >
> >> > SELECT "albums"."album_id", "albums"."name", "albums"."artist_id",
> >> > "relationship_artist"."name",
> >> > FROM "albums"
> >> > LEFT JOIN "artists"
> >> >  AS "relationship_artist"
> >> >  ON ("albums"."artist_id" = "relationship_artist"."artist_id")
> >> > WHERE "albums"."album_id" = 123
> >> >
> >> > (That might be an "alias", but I don't know if that's the right
> >> > terminology.)
> >> >
> >> > But I don't see how to do that with
> >> > gda_sql_builder_select_join_targets()
> >> > http://library.gnome.org/devel/libgda/unstable/GdaSqlBuilder.html#gda-sql-builder-select-join-targets
> >> >
> >>
> >> To do this you need to specify an alias when using the
> >> gda_sql_builder_select_add_target*() methods.
> >
> > But that's for adding table targts, right? How would it know which join
> > the alias name is for?
> >
> 
> The alias is for a target (table or sub select), not for a join: you
> don't need to name joins as they are never referenced anywhere else in
> the SQL statement, you can name targets if necessary.

In my example, the join name is mentioned in the list of fields. For
instance, relationship_artist.name.

Here's a silly example that shows the need for the name, because using
the table name would be ambiguous:

SELECT "albums"."album_id", "albums"."name",
"albums"."artist_singer_id", "relationship_artist_singer"."name",
"albums"."artist_drummer_id", "relationship_artist_drummer"."name",
  FROM "albums"
  LEFT JOIN "artists"
    AS "relationship_artist_singer"
    ON ("albums"."artist_singer_id" =
"relationship_artist_singer"."artist_id")
  LEFT JOIN "artists"
    AS "relationship_artist_drummer"
    ON ("albums"."artist_drummer_id" =
"relationship_artist_drummer"."artist_id")
WHERE "albums"."album_id" = 123




-- 
murrayc murrayc com
www.murrayc.com
www.openismus.com