On 17 May 2010 14:53, Murray Cumming <[email protected]> wrote: > On Mon, 2010-05-17 at 14:43 +0200, Vivien Malerba wrote: >> On 17 May 2010 14:39, Murray Cumming <[email protected]> wrote: >> > On Mon, 2010-05-17 at 14:20 +0200, Vivien Malerba wrote: >> >> On 17 May 2010 10:07, Murray Cumming <[email protected]> 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 >
This example illustrates why you need to be able to set targets' aliases. In this example you have the following targets: A: "albums" B: "artists" with the "relationship_artist_singer" alias C: "artists" with the "relationship_artist_drummer" alias and you reference the targets using their alias in the join condition and in the list of fields to be selected. and 2 joins (joinning conditions not mentionned here): A <--> B A <--> C the joins themselves are never referenced anywhere in the statement, so they are not named. Vivien _______________________________________________ gnome-db-list mailing list [email protected] http://mail.gnome.org/mailman/listinfo/gnome-db-list
