> I'm trying to automatically generate multi-table SQL
> statements from a Web environment, and am having a lot of
> difficulty doing it in a way that doesn't feel kludgy to
> me. I've looked at various sites and books, but they tend to
> focus on situations where multi-table joins are fixed--you
> want the same results every time, so form of the query never
> Suppose you have the archetypal CD database. If all the
> information is in a single table, you just have "SELECT
> artist, album, song FROM cd WHERE..." If you don't want the
> song, or want the label, you just drop or add it to the SELECT
> list. But if the data is in related tables, with, say, "album"
> as the main table, "artist" and "label" keyed to the album id,
> and "song" tied in via a separate join table, it gets much
> worse. If you only want the album, you have "SELECT album
> FROM album WHERE...", but if you want the artist too, it
> becomes "SELECT artist.artist, album.album FROM artist, album
> WHERE...AND artist.album_id = album.id", and so forth; if you
> want to search on "artist" and "song" only, you have to know that
> they can only be connected by bringing in "album" as well, so
> you end up with a four-table join.
> And this is a very simple example; real ones could involve
> larger sets of tables with more complex relationships that
> could be searched in more different ways.
> The way I've been working so far is to have an array
> tables_needed and push the table names onto it as they
> are required, and then uniquing the array at the end; and
> also having a table with the basic WHERE clause elements
> for the join (e.g. "artist.album_id = album.id") and
> similarly adding that to my constructed WHERE clause. But
> while this works, it looks ugly and is hard to maintain,
> and I also haven't been able to figure out (except by
> hard-coding all possible examples) how to bring in
> intermediate required tables, or how to handle the need
> to write certain queries as LEFT JOINs rather than INNER
> JOINs depending on the query.
> I'd be grateful for any suggestions for approaches, or
> pointers to sample code, etc.
> Jesse Sheidlower