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 =
FROM tblArtistTitles AS AT2
WHERE AT2.ArtistID = AT1.ArtistID
AND AT2.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.
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.