SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    $this->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Dec 2003
    Location
    Federal Way, Washington (USA)
    Posts
    1,524
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Nested Query Works in ASP but not PHP - Why?

    I'm running PHP 4.3.9. The following SQL statement works in my ASP script but will not work in PHP. Why? Here's the error:

    #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXISTS (SELECT tblTracks.AlbumID, tblTracks.ArtistFullName FROM
    Here's my SQL statement:

    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
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    Showcase your music collection on the Web

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    sounds like you're running those scripts on different versions of mysql

    run this query through each system and let us know what it produces each time:
    Code:
    select version()
    in any case i'm still trying to figure out your subquery and i think there may be another way to do what you want with a union instead of a subquery
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    $this->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Dec 2003
    Location
    Federal Way, Washington (USA)
    Posts
    1,524
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    sounds like you're running those scripts on different versions of mysql

    run this query through each system and let us know what it produces each time:
    Code:
    select version()
    Hmmm. That came back with 4.0.22-standard. Guess that means I can't run a nested query after all.

    If you could give me some guidance as to how I can get the same result without the nesting, I sure would appreciate it. Thanks.
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    Showcase your music collection on the Web

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, let me run through the logic of your query as it is currently written

    you want album and artist data for all albums where the album was done by a specific artist OR where there exists a track on the album which was done by the same artist? like in a collection of chrismas hits by various artists?

    if i understand this correctly, then you can do it like this --
    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%'
    )
    union 
    (
    select tblAlbums.Title 
         , tblAlbums.TitleSort
         , tblAlbums.MediaType 
         , tblAlbums.AlbumID 
         , tblArtists.The 
         , tblArtists.Artist 
         , tblArtists.ArtistID 
         , tblArtists.SortName 
      from tblArtists
    inner 
      join tblTracks   
        on tblAlbums.AlbumID 
         = tblTracks.AlbumID 
     where tblTracks.ArtistFullName like '%Willie Nelson%'
    )    
    order 
        by sortName
         , title
         , format
    note that UNION eliminates duplicates, which i think might come about because the artist's fullname is likely going to also be on all tracks where he's the artist on the album
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    $this->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Dec 2003
    Location
    Federal Way, Washington (USA)
    Posts
    1,524
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You've described the problem accurately. When I run the query as you wrote it in phpMyAdmin, I get the following error:

    #1109 - Unknown table 'tblAlbums' in field list
    I have no idea what it's complaining about though. Your query looks fine to me.
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    Showcase your music collection on the Web

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    doh!! i didn't even notice

    okay, a change to the second query is required

    right now it finds a track with the artist's name, but in order to return the album information that this track belongs to, it must also join to the albums table, which means there must be 3 tables in that query, properly joined

    does this make sense?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    $this->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Dec 2003
    Location
    Federal Way, Washington (USA)
    Posts
    1,524
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    does this make sense?
    Yep, makes perfect sense, but how to do it? I can't seem to get it right.

    Just in case it makes any difference, what I want to end up with is just a list of artists and albums.
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    Showcase your music collection on the Web

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    ...
    union 
    (
    select tblAlbums.Title 
         , tblAlbums.TitleSort
         , tblAlbums.MediaType 
         , tblAlbums.AlbumID 
         , tblArtists.The 
         , tblArtists.Artist 
         , tblArtists.ArtistID 
         , tblArtists.SortName 
      from tblTracks  
    inner
      join tblAlbums  
        on tblTracks.AlbumID 
         = tblAlbums.AlbumID 
    inner
      join tblArtists  
        on tblAlbums.ArtistID
         = tblArtists.ArtistID 
     where tblTracks.ArtistFullName like '%Willie Nelson%'
    )    
    order 
        by sortName
         , title
         , format
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    $this->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Dec 2003
    Location
    Federal Way, Washington (USA)
    Posts
    1,524
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Worked like a charm, Rudy. Many thanks for your help!

    You are well deserving of the Database Guru of the Year moniker.
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    Showcase your music collection on the Web


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •