mysql - 在连接上使用时的未知列

我一直在研究使用连接而不是逗号连接所导致的问题。我的SQL当前如下所示：

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

执行时，我得到以下错误：
#1054 - Unknown column 'island.islandID' in 'on clause'
我做了一些研究，了解了错误的根源，但是我试图通过为“island”表创建一个别名来纠正这个问题。当我这样做时，像“island.longDesc”这样的列就变成了“unknown”。
如果有人能纠正似乎是一个小语法问题，我将非常感谢。

Images Structure:
CREATE TABLE `images` (
  `imageID` int(11) NOT NULL auto_increment,
  `imageType` int(11) NOT NULL COMMENT 'used to tell if its for an artist, header image, etc.',
  `parentObjectID` int(11) NOT NULL COMMENT 'used to tell what island/resort the image applies to',
  `imageLocation` text NOT NULL,
  `largeImageLocation` text NOT NULL,
  `imageLinkLabel` text NOT NULL,
  `imageURL` text NOT NULL,
  PRIMARY KEY  (`imageID`)
)

Island Structure:
CREATE TABLE `island` (
  `islandID` int(11) NOT NULL auto_increment,
  `islandName` text NOT NULL,
  `shortDesc` text NOT NULL,
  `longDesc` text NOT NULL,
  `getTo` text NOT NULL,
  `getAround` text NOT NULL,
  `photoInfo` text NOT NULL,
  `flowerInfo` text NOT NULL,
  `musicInfo` text NOT NULL,
  `cakeInfo` text NOT NULL,
  `activityInfo` text NOT NULL,
  `wedCoord` text NOT NULL,
  `regs` text NOT NULL,
  `climate` text NOT NULL,
  `languageID` int(11) NOT NULL,
  `currencyID` int(11) NOT NULL,
  `wideAccept` int(11) NOT NULL,
  `passportReq` int(11) NOT NULL,
  `picture` text NOT NULL,
  `daysSearchable` int(11) NOT NULL,
  `active` tinyint(1) NOT NULL,
  PRIMARY KEY  (`islandID`)
)

Resort Structure:
CREATE TABLE `resort` (
  `resortID` int(11) NOT NULL auto_increment,
  `resortName` text NOT NULL,
  `parentIslandID` int(11) NOT NULL,
  `longDesc` text NOT NULL,
  `website` text NOT NULL,
  `genBooking` text NOT NULL,
  `eventCoord` text NOT NULL,
  `amenInfo` text NOT NULL,
  `roomInfo` text NOT NULL,
  `coordInfo` text NOT NULL,
  `localeInfo` text NOT NULL,
  `spaInfo` text NOT NULL,
  `roomPrice` text NOT NULL,
  `maxGuests` text NOT NULL,
  `picture` text NOT NULL,
  `search_Inclusive` int(11) NOT NULL,
  `search_resortType` int(11) NOT NULL,
  `search_onBeach` int(11) NOT NULL,
  `search_wedCoord` int(11) NOT NULL,
  `search_roomRate` int(11) NOT NULL,
  `search_airportDist` int(11) NOT NULL,
  `search_HotelSuite` tinyint(1) NOT NULL,
  `search_VillaCondo` tinyint(1) NOT NULL,
  `search_Amenities` text NOT NULL,
  `active` tinyint(1) NOT NULL,
  PRIMARY KEY  (`resortID`)
)

最佳答案

您正在混合两种不同类型的JOIN语法-表中列出的隐式类型“cc>”和显式FROM类型。相反，请尝试：

SELECT
   islandID AS parentIslandID,
   islandName,
   island.longDesc,
   imageLocation,
   COUNT(r1.resortID) AS totalResorts,
   r1.resortID
FROM island
  JOIN resort r1 ON island.islandID = r1.parentIslandID
  JOIN images ON island.islandID = images.parentObjectID
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
  )
AND resort.active = '-1'
GROUP BY r1.parentIslandID
ORDER BY totalResorts DESC

**在表结构发布后编辑以包含islandJOIN。
另外：
MySQL对boolean和
表别名不需要JOIN关键字（AND）
确保在选择列表中使用您的&&别名AS（JOIN resort r1）

关于mysql - 在连接上使用时的未知列，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/7169640/