Hi all,

I'm trying to do a select within a select and having problems with it. First and foremost, my MS Access db I'm using is the back end to some purchased software, so modifying the table structure is out of the question.

That said though, here is my table structure:

tblArists can have 1 to many tblAlbums entries
tblAlbums can have 1 to many tblTracks entries

tblArtists and tblAlbums are joined by ArtistID
tblAlbums and tblTracks are joined by AlbumID

I want to get a list of albums by artists where either the "album artist" or "track artist" has a certain value. My query as it sits right now is bringing every album in the database. Somehow I need to adjust the syntax to specify that the artist in the main select must match the artist in the sub-select but I have no idea how to do that. Here's the statement as it exists. Perhaps someone can tell me how to fix it. Thanks.

SELECT tblAlbums.Title AS title,
tblAlbums.TitleSort,
tblAlbums.MediaType AS format,
tblAlbums.AlbumID AS albumid,
tblArtists.The AS artist_the,
tblArtists.Artist AS artist,
tblArtists.ArtistID as artistid,
tblArtists.SortName AS sortname
FROM tblArtists INNER JOIN tblAlbums
ON tblArtists.ArtistID = tblAlbums.ArtistID
WHERE EXISTS
(SELECT tblAlbums.AlbumID,
tblAlbums.ArtistID,
tblArtists.ArtistID,
tblArtists.Artist,
tblArtists.The,
tblTracks.AlbumID,
tblTracks.ArtistFullName
FROM (tblArtists INNER JOIN tblAlbums
ON tblArtists.ArtistID = tblAlbums.ArtistID)
LEFT JOIN tblTracks ON tblAlbums.AlbumID = tblTracks.AlbumID
WHERE (tblArtists.Artist LIKE '%Willie Nelson%' OR tblTracks.ArtistFullName LIKE '%Willie Nelson%') )
ORDER BY tblArtists.SortName, tblAlbums.Title, tblAlbums.MediaType;



Pat Wong
http://www.napathon.net/
-------------------------------------------------------------------
Senility Prayer:
God grant me the senility to forget the people I never
liked anyway, the good fortune to run into the ones I
like, and the eyesight to tell the difference.

__________________
Pat Wong
http://www.napathon.net/ - Music Around The World
For collecting tips, trade and want lists, album reviews and more.

Can you not just use a 3 table join?

Try something like:

SELECT * FROM
tblArtists art, tblAlbums alb, tblTracks trk
WHERE art.ArtistID = alb.ArtistID
  AND alb.AlbumID = trk.AlbumID
  AND (art.Artist LIKE '%Willie Nelson%'
  OR trk.ArtistFullName LIKE '%Willie Nelson%')

--I'll leave it up to you to alias your columns...

Your nested SELECT doesn't have any link between the two SQL statements, you could probably have added an alias to the 2 references to the Artist table and then set them equal, but all that INNER JOIN ON syntax makes me dizzy.

Let me know how it works.

I am not sure I fully understand your intended result but you might try using the IN operator to only return the data for the rows in the subquery.

You probably need to modify this but it should go something like:

SELECT
 tblAlbums.Title AS title,
 tblAlbums.TitleSort,
 tblAlbums.MediaType AS format,
 tblAlbums.AlbumID AS albumid,
 tblArtists.The AS artist_the,
 tblArtists.Artist AS artist,
 tblArtists.ArtistID as artistid,
 tblArtists.SortName AS sortname

FROM tblArtists INNER JOIN tblAlbums
     ON tblArtists.ArtistID = tblAlbums.ArtistID

WHERE AlbumID IN
(
SELECT tblAlbums.AlbumID
   FROM
     tblArtists INNER JOIN tblAlbums ON
     tblArtists.ArtistID = tblAlbums.ArtistID
     LEFT JOIN tblTracks ON tblAlbums.AlbumID = tblTracks.AlbumID
   WHERE
     (tblArtists.Artist LIKE '%Willie Nelson%' OR
      tblTracks.ArtistFullName LIKE '%Willie Nelson%')
)
ORDER BY tblArtists.SortName, tblAlbums.Title, tblAlbums.MediaType;

Watch my syntax etc but the idea is to identify the rows you want in the the subquery and pull the appropriate data from the parent

Hope I understood your question and this helps

HR

For starters, in your WHERE EXISTS (which is faster than using IN), you only need to return one column (or NULL), not *all* your columns:

WHERE EXISTS(
SELECT NULL
FROM myTable AS b
WHERE a.ID = b.ID
) -- where a is a table in your *outer* query.

Cheers
Ken

I already tried that method, and it won't work. The error message I get says that AlbumID is referenced in more than one table.

Here's what I did that finally worked. BTW, there is an example in the Wrox book Beginning SQL Programming, page 147, that's very similar to what I am trying to do. I read and re-read it, and then it finally sunk in! Anyway, here's my working code:

SELECT tblAlbums.Title AS title,
tblAlbums.TitleSort,
tblAlbums.MediaType AS format,
tblAlbums.AlbumID AS albumid,
tblArtists.The AS artist_the,
tblArtists.Artist AS artist,
tblArtists.ArtistID as artistid,
tblArtists.SortName AS sortname
FROM tblArtists
INNER JOIN tblAlbums ON tblArtists.ArtistID = tblAlbums.ArtistID WHERE (tblArtists.Artist LIKE '%Willie Nelson%' )
OR EXISTS
(SELECT tblTracks.AlbumID,
tblTracks.ArtistFullName
FROM tblTracks
WHERE (tblArtists.Artist LIKE '%Willie Nelson%'
OR tblTracks.ArtistFullName LIKE '%Willie Nelson%')
AND tblTracks.AlbumID = tblAlbums.AlbumID)
ORDER BY tblArtists.SortName, tblAlbums.Title, tblAlbums.MediaType;

Pat Wong
http://www.napathon.net/ - Music Around The World
For collecting tips, trade and want lists, and more.

  Ken,

Sorry I didn't read your reply before I posted. Nice to see you here in the brand new forum!

Yes, the WHERE EXISTS is definitely fast. For my working query, I'm spinning through about 12,000+ tblTracks records, but my web page that I'm displaying the data in is still coming back pretty fast. :D

Pat Wong
http://www.napathon.net/ - Music Around The World
For collecting tips, trade and want lists, and more.

OK, folks, I need a fresh set of eyes to look at some similar code to the one I posted in this thread a few days ago. It's doing the same sort of selection from the same database, just different criteria. I have no idea what I'm doing wrong, but I'm getting the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

Here's the new code, too. Any help is greatly appreciated!

SELECT tblAlbums.Title AS title,
tblAlbums.TitleSort,
tblAlbums.MediaType AS format,
tblAlbums.AlbumID AS albumid,
tblArtists.The AS artist_the,
tblArtists.Artist AS artist,
tblArtists.ArtistID as artistid,
tblArtists.SortName AS sortname
FROM tblArtists
INNER JOIN tblAlbums ON tblArtists.ArtistID = tblAlbums.ArtistID WHERE
(tblArtists.Artist LIKE '%Bee Gees%'
OR tblArtists.Artist LIKE '%Andy Gibb%'
OR tblArtists.Artist LIKE '%Barry Gibb%'
OR tblArtists.Artist LIKE '%Maurice Gibb%'
OR tblArtists.Artist LIKE '%Robin Gibb%' )
OR EXISTS
(SELECT tblTracks.AlbumID,
tblTracks.Composer
FROM tblTracks
WHERE
(tblArtists.Artist LIKE '%Bee Gees%'
OR tblArtists.Artist LIKE '%Andy Gibb%'
OR tblArtists.Artist LIKE '%Barry Gibb%'
OR tblArtists.Artist LIKE '%Maurice Gibb%'
OR tblArtists.Artist LIKE '%Robin Gibb%'
OR tblTracks.Composer LIKE '%Bee Gees%'
OR tblTracks.Composer LIKE '%Andy Gibb%'
OR tblTtracks.Composer LIKE '%Barry Gibb%'
OR tblTracks.Composer LIKE '%Maurice Gibb%'
OR tblTracks.Composer LIKE '%Robin Gibb%' )
AND tblTracks.AlbumID = tblAlbums.AlbumID)
ORDER BY tblArtists.SortName, tblAlbums.Title, tblAlbums.MediaType;

Pat Wong
http://www.napathon.net/ - Music Around The World
For collecting tips, trade and want lists, album reviews and more.