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)
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.
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.
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