MySQL Forums
Forum List  »  PHP

Re: "In" Statement Returning 6 Records…overall SQL statement returns 22
Posted by: Aaron Falcone
Date: August 23, 2011 07:48PM

Ok I've been working on integrating Rick James's suggestions into my SQL code and have been working through more issues that resulted from using Join-Ons instead of the comma joins. My SQL currently looks like this:

SELECT islandID AS parentIslandID, islandName, island.longDesc,
imageLocation, COUNT(resort.resortID) AS totalResorts, resort.resortID
FROM island, resort, images
join resort as r1
on island.islandID = resort.parentIslandID
where
r1.resortID IN (
59,62,65,69,71,72,74,75,76,82,86,89,91,93,95,105,
106,116,117,118,120,121,122,123,124,125,126,127,
131,145,146,150,157,159,160,167,170,174,176,185,188,189,193,
194,198,199,200,203,205,213,217
)
&& resort.active = '-1'
GROUP BY resort.parentIslandID
ORDER BY totalResorts DESC

When executed, I get the following error:
#1054 - Unknown column 'island.islandID' in 'on clause'

I did some research and understand the origin of the error however I've tried to correct the issue by creating an alias for the "island" table. When I do this, columns like "island.longDesc" are then "unknown".

If anyone could correct what seems to be a minor syntax problem I would greatly appreciate it. I'm more used to using comma joins so bare with me. Thanks for any help-

-Aaron

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.