I have 3 tables I'm working with on a Select statement - tblArtists, tblAlbums, and tblTracks.
Each artist can have one to many albums. Each album can have one to many tracks.

I'm doing a search where I want to select Artist and Album when the "Album Artist" or the
"Track Artist" fits a certain criteria, BUT I don't want to list every single song on the
album, as it's doing right now.

Someone told me I need a select within a select, but I have no idea of the syntax on that.
Here's an example of the SELECT statement that gets me every song listed on the albums for a
particular artist. How do I change it so I'll only get each Album for the Artist? Thanks in
advance for your help.

SELECT tblAlbums.AlbumID,
tblAlbums.MediaType AS format,
tblAlbums.ArtistID,
tblAlbums.Title AS albumtitle,
tblAlbums.Total_Playing_Time AS albumtime,
tblArtists.ArtistID,
tblArtists.Artist AS artist,
tblArtists.The AS artist_the,
tblArtists.SortName,
tblTracks.AlbumID,
tblTracks.TrackID AS trackid,
tblTracks.Title AS tracktitle,
tblTracks.Duration AS tracktime,
tblTracks.ArtistFullName as track_artist,
tblTracks.Composer AS composer
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;


~8^) Pat Wong (ICQ #61070813)
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.
-------------------------------------------------------------------

The format for select within a select is as below

select a.field1 a,b.field1
from table1 a,(select * from table2) b

basically you will be including the select statement in the From Clause.

Rgds,
Raghu



-----Original Message-----
From: Pat Wong [mailto:vinyl-junkie@n...]
Sent: Monday, March 10, 2003 03:33
To: ASP Databases
Subject: [asp_databases] Select Within A Select - Syntax?


I have 3 tables I'm working with on a Select statement - tblArtists,
tblAlbums, and tblTracks.
Each artist can have one to many albums. Each album can have one to many
tracks.

I'm doing a search where I want to select Artist and Album when the "Album
Artist" or the
"Track Artist" fits a certain criteria, BUT I don't want to list every
single song on the
album, as it's doing right now.

Someone told me I need a select within a select, but I have no idea of the
syntax on that.
Here's an example of the SELECT statement that gets me every song listed on
the albums for a
particular artist. How do I change it so I'll only get each Album for the
Artist? Thanks in
advance for your help.

SELECT tblAlbums.AlbumID,
tblAlbums.MediaType AS format,
tblAlbums.ArtistID,
tblAlbums.Title AS albumtitle,
tblAlbums.Total_Playing_Time AS albumtime,
tblArtists.ArtistID,
tblArtists.Artist AS artist,
tblArtists.The AS artist_the,
tblArtists.SortName,
tblTracks.AlbumID,
tblTracks.TrackID AS trackid,
tblTracks.Title AS tracktitle,
tblTracks.Duration AS tracktime,
tblTracks.ArtistFullName as track_artist,
tblTracks.Composer AS composer
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;


~8^) Pat Wong (ICQ #61070813)
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.
-------------------------------------------------------------------