两次使用子查询的MySQL查询

Question

我有这个问题

SELECT itemID, locationParentID, locationID, categoryParentID, categoryID, itemTitle, itemDetails, itemAttributes, itemPictures, itemFeatured, itemSpotlight, itemAdded 
FROM items 
WHERE locationID NOT IN
    (SELECT locationID 
    FROM sites_locations 
    WHERE siteID != 2) AND items.siteID IN('1,2') AND itemStatus = '1' 
    AND itemAdded > '1356048000'

它工作正常但是当我执行这个查询时它失败了

SELECT itemID, locationParentID, locationID, categoryParentID, categoryID, 
itemTitle, itemDetails, itemAttributes, itemPictures, itemFeatured, itemSpotlight, itemAdded FROM items 
WHERE locationID NOT IN
    (SELECT locationID 
     FROM sites_locations WHERE siteID != 2) 
     AND categoryID NOT IN (SELECT categoryID FROM sites_categories 
     WHERE siteID != 2) AND items.siteID IN('1,2') AND itemStatus = '1' 
     AND itemAdded > '1356048000' 

这里的区别是我添加了子句

AND categoryID NOT IN(SELECT categoryID FROM sites_categories 
WHERE siteID != ".$cfg['site']['siteID'].") 

知道为什么这行不通吗？

或者更好的是，一个 Join Query 来帮助优化它。

sites_categories的表结构，数据在

items的表结构，数据在

sites_locations 的表结构

位置表结构，数据在

提前致谢。

Answer 1

试试这个

SELECT i.itemID, i.locationParentID, i.locationID, i.categoryParentID, i.categoryID, 
i.itemTitle, i.itemDetails, i.itemAttributes, i.itemPictures, i.itemFeatured, i.itemSpotlight, i.itemAdded 
FROM    items i,  sites_locations sl, sites_categories sc  
WHERE i.locationID = sl.locationID 
AND i.categoryID = sc.categoryID   
AND i.siteID = 1 AND i.itemStatus = '1' 
AND i.itemAdded > '1356048000'

它可能有一些错误或与列名相关的错误请更正这些错误