SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast cliffgs's Avatar
    Join Date
    Aug 2003
    Location
    West Coast, New Zealand
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

  2. #2
    SitePoint Enthusiast cliffgs's Avatar
    Join Date
    Aug 2003
    Location
    West Coast, New Zealand
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cliffgs View Post
    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

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •