Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 3rd, 2003, 09:40 PM
Authorized User
 
Join Date: Jun 2003
Location: Federal Way, WA (USA)
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to vinyl-junkie
Default Select Within A Select Problem

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.
Reply With Quote
  #2 (permalink)  
Old June 4th, 2003, 05:11 AM
Registered User
 
Join Date: Jun 2003
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
Reply With Quote
  #3 (permalink)  
Old June 4th, 2003, 11:16 PM
Registered User
 
Join Date: Jun 2003
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #4 (permalink)  
Old June 5th, 2003, 09:09 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Sydney, NSW, Australia.
Posts: 111
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #5 (permalink)  
Old June 5th, 2003, 09:13 PM
Authorized User
 
Join Date: Jun 2003
Location: Federal Way, WA (USA)
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to vinyl-junkie
Default

Quote:
quote:WHERE AlbumID IN
(
SELECT tblAlbums.AlbumID
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.
Reply With Quote
  #6 (permalink)  
Old June 5th, 2003, 09:19 PM
Authorized User
 
Join Date: Jun 2003
Location: Federal Way, WA (USA)
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to vinyl-junkie
Default

Quote:
quote:Originally posted by KenSchaefer
 For starters, in your WHERE EXISTS (which is faster than using IN),
  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.
Reply With Quote
  #7 (permalink)  
Old June 7th, 2003, 04:31 PM
Authorized User
 
Join Date: Jun 2003
Location: Federal Way, WA (USA)
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to vinyl-junkie
Default

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.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Select row in GridView WITHOUT using Select button rao965 ASP.NET 2.0 Professional 1 February 15th, 2008 10:44 AM
Select from another select statement to a repeater simsen ASP.NET 2.0 Professional 0 May 2nd, 2007 04:34 PM
Fill select box and select recordset value markd Classic ASP Databases 1 February 20th, 2006 06:41 PM
select problem mildge SQL Language 2 May 18th, 2004 06:35 PM
select="node1", select="node2"... Baldo XSLT 7 March 12th, 2004 10:38 AM



All times are GMT -4. The time now is 11:24 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.