java - 无法弄清楚为什么这个大的双内部连接不起作用，但是当拆分时它会起作用

Question

这是我的查询拆分后工作得很好...

    String sqlstatement = "SELECT WBLinkWebsiteID, WBLinkCategoryParentID, WBLinkTitle, WBLinkURL FROM WEBSITECATEGORY_TABLE WHERE WBLinkCategoryID = ?";
    String[] args = { CategorySubID };

第 2 部分

    sqlstatement = "SELECT LOCATIONS_TABLE.LocationWebsiteID, "
+ "LOCATIONS_TABLE.locationCity, "
+ "LOCATIONS_TABLE.locationState, "
+ "LOCATIONS_TABLE.locationCountry, LOCATIONS_TABLE.locationType, "
+ "LOCATIONS_TABLE.locationUrl, "
+ "PREF_TABLE.Pref_SavedTitle "
+ "FROM PREF_TABLE INNER JOIN "
+ "LOCATIONS_TABLE ON PREF_TABLE.Pref_LocationID = LOCATIONS_TABLE.LocationID "
+ "WHERE "
+ "PREF_TABLE.Pref_SavedTitle = '" + theSavedPref + "' ORDER BY LOCATIONS_TABLE.locationState, LOCATIONS_TABLE.locationCity";

现在我尝试将两者结合起来，而不是让 2 个背靠背排成一行并消耗时间/资源...

    String NewSqlstatement = "SELECT LOCATIONS_TABLE.LocationWebsiteID, "
+ "LOCATIONS_TABLE.locationCity, "
+ "LOCATIONS_TABLE.locationState, "      
+ "LOCATIONS_TABLE.locationCountry, "
+ "LOCATIONS_TABLE.locationUrl, "
+ "LOCATIONS_TABLE.LocationID, "
+ "PREF_TABLE.Pref_SavedTitle, "   
+ "WEBSITECATEGORY_TABLE.WBLinkTitle, "
+ "WEBSITECATEGORY_TABLE.WBLinkURL "
+ "FROM PREF_TABLE INNER JOIN "   
+ "LOCATIONS_TABLE ON PREF_TABLE.Pref_LocationID = LOCATIONS_TABLE.LocationID "
+ "INNER JOIN WEBSITECATEGORY_TABLE "
+ "ON WEBSITECATEGORY_TABLE.WBLinkWebsiteID = PREF_TABLE.Pref_WebsiteID "
+ "WHERE "
+ "PREF_TABLE.Pref_SavedTitle = '" + theSavedPref + "'";

现在，当我尝试执行我的“单一”方式时，它会不断在 LOCATIONS_TABLE 查询中返回整个位置数据库。它不只是我需要的。

我知道查询有效，因为我在这里测试过它:http://sqlfiddle.com/#!6/ede97/2

现在我知道我在 sqlfiddle 上的示例使用的是 MS Server 2014，但我认为语法应该几乎相同，因为它只是带有内部联接的标准 SELECT，但我可能是错的？

有人知道我做错了什么吗？非常感谢任何帮助

编辑 - 修复了 SQLFIDDLE，我在示例中放置了错误的语句

Answer 1

您是否缺少组合查询中 WBLinkCategoryID 的过滤器。你不应该有这个:


...
+ "INNER JOIN WEBSITECATEGORY_TABLE "
+ "ON WEBSITECATEGORY_TABLE.WBLinkWebsiteID = PREF_TABLE.Pref_WebsiteID "
+ "WHERE "
+ "WEBSITECATEGORY_TABLE.WBLinkCategoryID IN (<value1>,...,<valueN>) AND "
+ "PREF_TABLE.Pref_SavedTitle = '" + theSavedPref + "'";