Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2014
    Posts
    48

    Unanswered: Nested Subquery Issue

    Hi Guys,

    I'm having an issue with a nested subquery not returning the expected results and I'm hoping someone can offer some assistance in solving the problem.

    The key to the table in question is ArtistID, TitleID and YearReleased. All fields are Text, 4 positions long.

    The purpose of the query is to return a single album cover image for each artist. The Album Cover image is stored in the database in an OLE Object field.

    For most Artists ArtistID and TitleID would return a unique Album, but it is possible for an Artist to have more than 1 album with the same Title. Including YearReleased as part of the key helps to insure that these fields combined yield a unique Album Entity since it is highly unlikely that an Artist will have more than one Album with the same Title with the same YearReleased..

    Here's the query:

    SELECT ArtistID, CoverArt
    FROM tblArtistTitles AS AT1
    WHERE TitleID =
    (SELECT MIN(TitleID)
    FROM tblArtistTitles AS AT2
    WHERE AT2.ArtistID = AT1.ArtistID
    AND AT2.YearReleased =
    (SELECT MIN(YearReleased)
    FROM tblArtistTitles AS AT3
    WHERE AT3.ArtistID = AT2.ArtistID
    AND AT3.TitleID = AT2.TitleID));

    The purpose of the first subquery is to select a unique TitleID for each Artist by using MIN(TitleID)

    The purpose of the second sub query is to further filter the results from the first subquery by selecting the MIN(YearReleased) for each Artist and Title. This should result in only the Album with the MIN(YearReleased) being returned for those Artists that have more than one Album by the same Title while leaving the others as they are because they will have already returned a single record and that record will be evaluated as the record with the MIN(YearReleased) for that Artist.

    The problem with this nested subquery is that the second subquery which evaluated MIN(YearReleased) does not appear to be filtering out the records as expected. It returns the same number of records as a subquery which evaluates MIN(TitleID) alone does.

    Any assistance you can provide in solving this issue would be most helpful.

    Thanks

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    There is no need for using subqueries in this case, use INNER JOINs instead.
    Have a nice day!

  3. #3
    Join Date
    Sep 2014
    Posts
    48
    Please explain, I've tried every approach to this problem that I can think of. Could you please provide an example of the method you propose using the information I provided in my initial post. All table and field names are accurate.
    Last edited by VSCurtis; 01-04-15 at 03:25.

Posting Permissions

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