查询未返回预测结果-我缺少什么

Question

我运行以下查询：

SELECT [LISTING_ID] ,[COMPANY_NM]
FROM [InterAction_Test].[dbo].[INT_AUX_LISTING]
WHERE COMPANY_NM = 'IB Sample 12'

退货：

LISTING_ID   COMPANY_NM
----------   ----------
335698       IB Sample 12

然后：

SELECT [LIST1_ID],[LIST2_ID],[CORRELATION_TYP_ID]
FROM [InterAction_Test].[dbo].[INT_AUX_LIST_LIST]
WHERE LIST1_ID = 335698

退货：

LIST1_ID   LIST2_ID   CORRELATION_TYP_ID
--------   --------   ------------------
335698     139685     90
335698     139685     10003
335698     139685     10009

然后：

SELECT [LISTING_ID],[DISPLAY_NM]
FROM [InterAction_Test].[dbo].[INT_AUX_LISTING]
WHERE LISTING_ID = 139685

退货：

LISTING_ID   DISPLAY_NM
----------   ----------
139685       Swisk, Bob

然后我运行：

SELECT dbo.INT_AUX_LISTING.COMPANY_NM, INT_AUX_LISTING_1.DISPLAY_NM AS [Relationship Partner], 
INT_AUX_LISTING_2.DISPLAY_NM AS [Introducing Partner], INT_AUX_LISTING_3.DISPLAY_NM AS [Introduced By]
FROM dbo.INT_AUX_LISTING 
INNER JOIN dbo.INT_AUX_LIST_LIST ON dbo.INT_AUX_LISTING.COMPANY_LIST_ID = dbo.INT_AUX_LIST_LIST.LIST1_ID 
INNER JOIN dbo.INT_AUX_LISTING AS INT_AUX_LISTING_1 ON dbo.INT_AUX_LIST_LIST.LIST2_ID = INT_AUX_LISTING_1.LISTING_ID 
INNER JOIN dbo.INT_AUX_LIST_LIST AS INT_AUX_LIST_LIST_1 ON dbo.INT_AUX_LISTING.COMPANY_LIST_ID = INT_AUX_LIST_LIST_1.LIST1_ID 
INNER JOIN dbo.INT_AUX_LISTING AS INT_AUX_LISTING_2 ON INT_AUX_LIST_LIST_1.LIST2_ID = INT_AUX_LISTING_2.LISTING_ID 
INNER JOIN dbo.INT_AUX_LIST_LIST AS INT_AUX_LIST_LIST_2 ON dbo.INT_AUX_LISTING.COMPANY_LIST_ID = INT_AUX_LIST_LIST_2.LIST1_ID 
INNER JOIN dbo.INT_AUX_LISTING AS INT_AUX_LISTING_3 ON INT_AUX_LIST_LIST_2.LIST2_ID = INT_AUX_LISTING_3.LISTING_ID
GROUP BY dbo.INT_AUX_LISTING.COMPANY_LIST_ID, dbo.INT_AUX_LISTING.COMPANY_NM, INT_AUX_LISTING_1.DISPLAY_NM, INT_AUX_LISTING_2.DISPLAY_NM, INT_AUX_LISTING_3.DISPLAY_NM, dbo.INT_AUX_LIST_LIST.CORRELATION_TYP_ID, INT_AUX_LIST_LIST_1.CORRELATION_TYP_ID, INT_AUX_LIST_LIST_2.CORRELATION_TYP_ID
HAVING (dbo.INT_AUX_LIST_LIST.CORRELATION_TYP_ID = 90) AND (INT_AUX_LIST_LIST_1.CORRELATION_TYP_ID = 10003) AND (INT_AUX_LIST_LIST_2.CORRELATION_TYP_ID = 10009)

这不返回任何行，当我希望它返回时：

COMPANY_NM     Relationship Partner   Introducing Partner   Introduced By
----------     --------------------   -------------------   -------------
IB Sample 12   Swisk, Bob             Swisk, Bob            Swisk, Bob

我做错了什么？

Answer 1

我认为在最后一行，HAVING子句所在的位置，您需要OR而不是AND运算符。

像这样：

GROUP BY dbo.INT_AUX_LISTING.COMPANY_LIST_ID, dbo.INT_AUX_LISTING.COMPANY_NM, INT_AUX_LISTING_1.DISPLAY_NM, INT_AUX_LISTING_2.DISPLAY_NM, INT_AUX_LISTING_3.DISPLAY_NM, dbo.INT_AUX_LIST_LIST.CORRELATION_TYP_ID, INT_AUX_LIST_LIST_1.CORRELATION_TYP_ID, INT_AUX_LIST_LIST_2.CORRELATION_TYP_ID
HAVING (dbo.INT_AUX_LIST_LIST.CORRELATION_TYP_ID = 90) OR (INT_AUX_LIST_LIST_1.CORRELATION_TYP_ID = 10003) OR (INT_AUX_LIST_LIST_2.CORRELATION_TYP_ID = 10009)

Answer 2

在联接条件中，您使用的是[INT_AUX_LISTING].[Company_List_ID]，但在您提供的示例查询和数据中，值包含在[INT_AUX_LISTING].[Listing_ID]列中。

我创建了示例数据，并在[INT_AUX_LISTING].[Listing_ID]上将查询更改为join，它就工作了。请参阅SqlFiddle