Nested Query Works in ASP but not PHP - Why?

[vinyl-junkie]

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:

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

[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:

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

[vinyl-junkie]

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:

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.

[r937]

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 --

(
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

[vinyl-junkie]

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.

[r937]

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?

[vinyl-junkie]

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.

[r937]

...
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

[vinyl-junkie]

Worked like a charm, Rudy. Many thanks for your help!

You are well deserving of the Database Guru of the Year moniker.