Select Within A Select Problem

[vinyl-junkie]

Hi all,

I'm trying to do a select within a select and having problems with it. First of all, let me explain the table structure I'm referencing.

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

What I am trying to do is select artists who appear in the tblArtists.Artist or tblTracks.ArtistFullName fields in the database, then list only Artist, Album, and MediaType in a table. In order to do that, I need to do a select within a select. Here's the error I'm getting in my code:

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

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'tblAlbums.MediaType a (SELECT tblAlbums.Title, tblAlbums.TitleSort, tblAlbums.MediaType, tblAlbums.AlbumID, tblArtists.The, tblArtists.Artist, tblArtists.ArtistID, tblArtists.SortName FROM tblArtists INNER JOIN tblAlbums ON a.tblArtists.ArtistID = tblAlbums.'.

Here is my Select statement. Can someone tell me what I'm doing wrong? I would very much appreciate it. Thanks!

SELECT a.tblAlbums.AlbumID,
a.tblAlbums.MediaType,
a.tblAlbums.ArtistID,
a.tblAlbums.Title,
a.tblArtists.ArtistID,
a.tblArtists.Artist,
a.tblArtists.The,
a.tblArtists.SortName,
a.tblTracks.AlbumID,
a.tblTracks.Title
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, tblTracks.Title, tblAlbums.MediaType a
(SELECT tblAlbums.Title,
tblAlbums.TitleSort,
tblAlbums.MediaType,
tblAlbums.AlbumID,
tblArtists.The, tblArtists.
Artist, tblArtists.ArtistID,
tblArtists.SortName
FROM tblArtists INNER JOIN tblAlbums
ON a.tblArtists.ArtistID = tblAlbums.ArtistID
ORDER BY tblArtists.SortName, tblAlbums.Title, tblTracks.Title, tblAlbums.MediaType b);

[raf]

Welcome here.

I don't see why you need a subselect. A simple three table join should do the trick. No?
Whats that a.tblAlbums.AlbumID doing there? or the a and b at the end of the order by cluauses?
And you can't link selects like that. With subselects, you need something like " where albumID IN (SELECT albumID from tracks where ...)
Also, the fact that you have a tblTracks.ArtistFullName column, means your db isn't designed optimal. You most likely only need the albumID there. In the albums table, you only need the artistsID. You can then get all the info from the album or artist, starting from the track, by joining the tables (--> a snowflake design). Or you could go for a start-design and have 1 central table (tracks) which contain the album and artists id (where the other tables are thought of as 'dimensions')
Also, you shouldn't use an outer join (the left join) --> you'll get unmatched records by definition and that's not what you want, i suppose.
Also, you'll get the artistID and the albumID twice in your recordset, which is not necessary (+whats the sortname?)
Also, the where clause --> when you select the artist, you should only pass the artists ID to the asp page that makes the select, not the name. A select on a numerical variable will go much faster then a select on a string variable (certainly if you need to use the LIKE operator and check for values in two cuolumns) + it would save you a join (if you drag the artists info along in a hidden formfield or in the querystring.) So it would be reduced to a two table join (which will perform better) because you have the artistID inside the albums id.

To get the albumtitle, albumID and mediatype then, you'd simply need :

sql="SELECT tblAlbums.AlbumID, tblAlbums.MediaType, tblAlbums.Title FROM (tbltracks INNER JOIN tblAlbums ON tblTracks.AlbumID=tblAlbums.AlbumID) WHERE tblAlbums.ArtistID=theid ORDER BY tblAlbums.Title, tblAlbums.MediaType"
sql=replace(sql,"theid",request.form("artist"))

The second line (replace) replaces the 'theid' by the ID that was selected in the form

Let us know if you need more info or if i'm completely off here.

[vinyl-junkie]

Thanks for your reply. One of the things that I should have mentioned in my original post is that my MS Access database is the back end to some purchased software, so there's no modifying the table structure.

One of the things you mentioned:something like " where albumID IN (SELECT albumID from tracks where ...) set me to trying something in a slightly different direction. I'm not getting an error now, but albums are being listed multiple times, and I only want each album listed once. I think what's missing is to somehow specify that albumID in the main select equals albumID in the sub-select. However, I don't know how to do that. Anyway, here's an example of the SQL as it sits now. Maybe you can tell me how to specify that additional criteria?

SELECT tblAlbums.AlbumID,
tblAlbums.MediaType as format,
tblAlbums.ArtistID as artistid,
tblAlbums.Title as albumtitle,
tblArtists.ArtistID,
tblArtists.Artist as artist,
tblArtists.The as artist_the,
tblArtists.SortName,
tblTracks.AlbumID
FROM (tblArtists INNER JOIN tblAlbums ON
tblArtists.ArtistID = tblAlbums.ArtistID)
LEFT JOIN tblTracks ON tblAlbums.AlbumID = tblTracks.AlbumID
WHERE EXISTS
(SELECT tblAlbums.AlbumID,
tblAlbums.ArtistID,
tblArtists.ArtistID,
tblArtists.Artist,
tblArtists.The, tblTracks.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;

[raf]

I see. Well, i don't mind. But your making it way to hard on yourself and this will never be a fast executed statement or a smootly runing app.

To get only one record for each album, you need to use a GROUP BY-clause, but with this query and the variabels you include in the recordset, that wount work. Since you use MsAccess, you could look into Data Shaping --> run a search her or on google or so for it. It's exactly what you need; a hierarchical recordset.
Or apply a more logical pageflow where you first select the artist, then display the albums, then the tracks. (see my previous post)

[vinyl-junkie]

At last I got my code to work! Don't know if the way I did it was what you were trying to tell me or not, I've had such a hard time figuring out how to set it up. I do *very* much appreciate your help, raf. Anyway, here's the 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;