Help with INNER JOIN

Hi

I am having trouble getting an inner join to work; up front I must say that I haven’t quite figured joins of any flavour out yet.

My SELECT statement looks like this:

SELECT artwork.artist_id, artwork_id, title, artwork_photo_filename, artist_lname FROM artwork INNER JOIN ref_artists ON ref_artists.artist_id = artist_id WHERE TRUE ORDER BY artist_lname

the two tables I am trying to join are:

artwork - which contains artist_id, artwork_id, title, and artwork_photo_filename (and a bunch of other fields)

and

ref_artists - which contains artist_id and artist_lname (and artist_fname)

In the select statement the ‘WHERE TRUE’ statement comes from Kevin Yank’s book and is holding the place of a WHERE clause which sometimes has other parts (including possible AND artist_id = x).

The reason I need this SELECT statement is because an artist may be added at any time and so naturally after a while the artist_id field is not in alphabetical order; and I am getting all my info from the artwork table in which there is only artist_id.

Many thanks in advance, and I can supply more info if needed.

Cliff

Hi

Sorry to reply to my own question, but whilst waiting for an answer I managed (somehow) to fix my problem with:

SELECT artwork.artist_id, artwork.artwork_id, artwork.title, artwork.artwork_photo_filename, ref_artists.artist_lname FROM ref_artists INNER JOIN artwork ON ref_artists.artist_id = artwork.artist_id WHERE TRUE ORDER BY ref_artists.artist_lname

I included the table name ‘artwork’ in ‘ON re_artists.artist_id = artwork.artist_id’

So I will have to remember to include the table name with all of column names in the future.

Many thanks

Cliff

not sure what your problem was, because it was not actually necessary to qualify the artist_id column with its table name, as this column is present in only one table, and hence there would be no query ambiguity in using the unqualified name

that having been said, however, the practice of qualifying ~all~ columns in a query with more than one table is very important, and should always be observed, even if all columns are uniquely named