Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2010
    Posts
    6

    Question Unanswered: LEFT JOIN Help needed for Access 2003 .MDB

    All,

    I'll try and keep this concise: I'm working on a VB6 application with an Access 2003 .MDB on the back end and ADO as the bridge between them. Although I'm looking for some help to get the SQL right for my ADO command, I'm attaching a sample database with some sample data here for those willing to help.

    The "Base" query involves the Artist, Song, Title and TitleSong tables and looks like this (This is the query "RatingBase" in the attached .MDB):

    SELECT Song.SongID, Song.SongName, Song.SongMinutes, Song.SongSeconds, Song.ArtistID, Artist.ArtistName, Title.TitleID, Title.TitleName, TitleSong.SongIndex, TitleArtist.ArtistID AS TitleArtistID, TitleArtist.ArtistName AS TitleArtistName
    FROM Title, Artist, Song, TitleSong, Artist AS TitleArtist
    WHERE (Artist.ArtistID=Song.ArtistID) And (Song.SongID=TitleSong.SongID) And (Title.TitleID=TitleSong.TitleID) And (Title.ArtistID=TitleArtist.ArtistID) And (((Song.SongName Like '*stone*')));

    The sample data won't copy very nicely here, but the gist of it is:

    SongID SongName SongMinutes SongSeconds ArtistID ArtistName
    12 Stoneface 2 44 1 Veruca Salt
    24 Stone Cold Crazy 2 17 2 Metallica
    29 Stone Dead Forever 4 51 2 Metallica
    44 Bloodstone 3 52 4 Judas Priest
    61 Stone Cold 5 17 15 Rainbow

    I have 2 new tables for rating songs, and I'm at the point where I've got the LEFT JOIN working enough to return me all Ratings for the base query, but the problem is that I can't filter this on the RatingID.

    Here's the query with my current LEFT JOIN (not returning the info I want) (This is the query "RatingLeftJoinInitial2WorkingRatingIDIncluded " in the included .MDB):

    SELECT Song.SongID, Song.SongName, Song.SongMinutes, Song.SongSeconds, Song.ArtistID, Artist.ArtistName, Title.TitleID, Title.TitleName, TitleSong.SongIndex, TitleArtist.ArtistID AS TitleArtistID, TitleArtist.ArtistName AS TitleArtistName, RatingItem.RatingValue, RatingItem.RatingID
    FROM Artist AS TitleArtist INNER JOIN (Title INNER JOIN (((Song INNER JOIN Artist ON Song.ArtistID = Artist.ArtistID) LEFT JOIN RatingItem ON Song.SongID = RatingItem.RatingItemTrackID) INNER JOIN TitleSong ON Song.SongID = TitleSong.SongID) ON Title.TitleID = TitleSong.TitleID) ON TitleArtist.ArtistID = Title.ArtistID
    WHERE (((Song.SongName) Like "*Stone*"));

    Again, here's the gist of the return:

    SongID SongName ArtistName TitleName TitleArtistName RatingValue RatingID
    12 Stoneface Veruca Salt Eight Arms To Hold You Veruca Salt 3 2
    12 Stoneface Veruca Salt Eight Arms To Hold You Veruca Salt 4 1
    24 Stone Cold Crazy Metallica Garage Inc. (Disc 2 of 2) Metallica 5 1
    29 Stone Dead Forever Metallica Garage Inc. (Disc 2 of 2) Metallica
    44 Bloodstone Judas Priest Screaming For Vengeance Judas Priest
    61 Stone Cold Rainbow Head Banging Metal Various Artists

    The problem here is that it returns values for all RatingIDs, when I only want the return for a specific on (let's say I want RatingID 1 only). All attempts I've made to filter on this have resulted in failure, so that's where I'm looking for help.

    What I *want* to see in my return is:

    SongID SongName ArtistName TitleName TitleArtistName RatingValue RatingID
    12 Stoneface Veruca Salt Eight Arms To Hold You Veruca Salt 4 1
    24 Stone Cold Crazy Metallica Garage Inc. (Disc 2 of 2) Metallica 5 1
    29 Stone Dead Forever Metallica Garage Inc. (Disc 2 of 2) Metallica
    44 Bloodstone Judas Priest Screaming For Vengeance Judas Priest
    61 Stone Cold Rainbow Head Banging Metal Various Artists

    where the RatingID is either 1 or NULL. Is this possible? Thanks for any help!!

    Mike S.
    Attached Files Attached Files

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Well you've pretty well got the answer right here:
    Quote Originally Posted by mikeslavis View Post
    where the RatingID is either 1 or NULL.
    Just for giggles, try to insert this logic in to your last query and we'll iron out any errors.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2010
    Posts
    6

    Smile Ha! Thanks!

    Pootle, you nailed it...I was so close, what can I say? I guess I gave up too soon and/or couldn't see the forest through the trees. At any rate, THANK YOU. I'm glad this could be done. I changed the WHERE clause to:

    WHERE (((Song.SongName) Like "*Stone*") AND ((RatingItem.RatingID)=1 Or (RatingItem.RatingID) Is Null))

    ...and it worked like a charm. Yea! Now I get the results I wanted with just 1 query, fairly efficiently. Here's the whole SQL:

    SELECT Song.SongID, Song.SongName, Artist.ArtistName, Title.TitleName, TitleArtist.ArtistName AS TitleArtistName, RatingItem.RatingValue, RatingItem.RatingID
    FROM Artist AS TitleArtist INNER JOIN (Title INNER JOIN (((Song INNER JOIN Artist ON Song.ArtistID = Artist.ArtistID) LEFT JOIN RatingItem ON Song.SongID = RatingItem.RatingItemTrackID) INNER JOIN TitleSong ON Song.SongID = TitleSong.SongID) ON Title.TitleID = TitleSong.TitleID) ON TitleArtist.ArtistID = Title.ArtistID
    WHERE (((Song.SongName) Like "*Stone*") AND ((RatingItem.RatingID)=1 Or (RatingItem.RatingID) Is Null));

    Snippet of result set:

    SongID SongName ArtistName TitleName TitleArtistName RatingValue RatingID
    12 Stoneface Veruca Salt Eight Arms To Hold You Veruca Salt 4 1
    24 Stone Cold Crazy Metallica Garage Inc. (Disc 2 of 2) Metallica 5 1
    29 Stone Dead Forever Metallica Garage Inc. (Disc 2 of 2) Metallica
    44 Bloodstone Judas Priest Screaming For Vengeance Judas Priest
    61 Stone Cold Rainbow Head Banging Metal Various Artists

    (No RatingID 2 included any longer).

    Also, thank you to Access for the graphical query editor, I don't think I could've ever constructed anything like that from "scratch".

    Mike S.

    -------------
    Quick Edit to add attached database with working query inside, named "RatingLeftJoinWorkingAccess"
    Attached Files Attached Files
    Last edited by mikeslavis; 02-12-10 at 09:49.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    One of the things with SQL is that it is a declarative language. This means you declare what you want (not how to get it like other languages).

    A consequence of this is that very often if you can clearly and concisely state what you want in English you are actually pretty close to the SQL that will get it for you. Worth bearing in mind
    Testimonial:
    pootle flump
    ur codings are working excelent.

Tags for this Thread

Posting Permissions

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