为什么我的嵌套分页查询出现"multi-part identifier could not be bound"错误？

Question

我对SQL相当陌生，我无法找出我的嵌套分页查询有什么问题。我一直在阅读这个错误，但我能理解解释，所以我不能在我的情况下应用它。我使用MS SQL 2008。我的表

[ContentDatabase]。[dbo]。[3DContent]有列(ContentId, Name, PublishTypeId, CategoryId)和键是ContentId表可能有其他列与问题无关

[ContentDatabase]。[dbo]。[SpecialCategoryMapping]有列(ContentId, SpecialCategoryId)，没有键，因为内容可以在多个类别中，但看看我的小数据集，我目前仍然有一个一对一的关系

[ContentDatabase]。[dbo]。[SpecialCategory]有列(SpecialCategoryId, ParentCategoryId, Name)和SpecialCategoryId是关键，我在这个查询中还没有使用ParentCategoryId

就像给定3dcontent名称的一部分的搜索功能一样，我的目标是返回3dcontent表的列及其匹配的CategoryName作为返回结果的另一个连接列。使用通过映射表解析的特殊类别id从特殊类别表中检索名称。所以我的查询在下面，但它返回一个错误，说多部分标识符不能绑定…

SELECT * FROM
   (SELECT A.[ContentId], A.[Name], A.[PublishTypeId], A.[CategoryId], B.[Name] as CategoryName, ROW_NUMBER() OVER (ORDER BY ContentID DESC) AS RowNum FROM [ContentDatabase].[dbo].[3DContent] AS A,
      (SELECT B.[Name] FROM [Contentatabase].[dbo].[SpecialCategory] as B WHERE B.[SpecialCategoryID] IN (SELECT * FROM [ContentDatabase].[dbo].[SpecialCategoryMapping] AS C WHERE C.[ContentID] = A.[ContentID])) AS D 
    WHERE A.Deleted = 0 AND A.PublishTypeId = 1 and A.[Name] like '%somepartialsearchstring%') as FinalTable 
 WHERE RowNum>=1 and RowNum<=6

可能有更好的方法来做这件事。我甚至不能检查我的查询结果，因为这个错误阻止了我。

感谢使用join的建议，我有以下内容，但它返回模棱两可的列错误似乎指向ContentId，

SELECT * FROM
   (SELECT A.[ContentId], A.[Name], A.[PublishTypeId], A.[CategoryId], A.[CategoryName], ROW_NUMBER() OVER (ORDER BY ContentID DESC) AS RowNum FROM 
       (SELECT Z.[ContentId], Z.[Name], Z.[PublishTypeId], Z.[CategoryId], Y.[Name] as CategoryName FROM [ContentDatabase].[dbo].[3DContent] AS Z LEFT OUTER JOIN [ContentDatabase].[dbo].[SpecialCategoryMapping] AS Y ON Z.[ContentId] = Y.[ContentId]) AS A 
    WHERE A.Deleted = 0 AND A.PublishTypeId = 1 and A.[Name] like '%somepartialsearchstring%') as FinalTable 
 WHERE RowNum>=1 and RowNum<=6

修复了一些错误后，它非常接近我需要的，它只是为所有CategoryName返回null，我也从Unnikrishnan给出的答案中删除了B. name，因为表B不包含名称列和a . name已经被选中。

SELECT * FROM 
    (SELECT A.[ContentId], A.[Name], A.[PublishTypeId] , A.[CategoryId], C.[Name] as CategoryName, ROW_NUMBER() OVER (ORDER BY A.ContentID DESC) AS RowNum FROM [ContentDatabase].[dbo].[3DContent] AS A LEFT OUTER JOIN [ContentDatabase].[dbo].[SpecialCategoryMapping] AS B ON B.[ContentID] = A.[ContentID] LEFT OUTER JOIN [ContentDatabase].[dbo].[SpecialCategory] as C ON B.[SpecialCategoryID] = C.[SpecialCategoryID] 
    WHERE A.Deleted = 0 AND A.PublishTypeId = 1 and A.[Name] like '%somepartialsearchstring%') as FinalTable
WHERE RowNum>=1 AND RowNum<=6

结果现在看起来像这样

ContentId   Name    PublishTypeId   CategoryId  CategoryName RowNum
7594        Jet     1               12          NULL         1

但是从SpecialCategory表中我可以看到

SpecialCategoryID   Name
12                  Vehicles

所以我期待车辆在CategoryName。谢谢你的帮助。

Answer 1

试试下面的查询…

如果您需要选择类别名称对表[SpecialCategoryMapping]对contentID中的specialcategoryid，您可以使用下面的脚本。

    SELECT * FROM 
    (SELECT A.[ContentId], A.[Name], A.[PublishTypeId] , A.[CategoryId], C.[Name] as CategoryName
      , ROW_NUMBER() OVER (ORDER BY A.ContentID DESC) AS RowNum 
      FROM [ContentDatabase].[dbo].[3DContent] AS A 
          LEFT OUTER JOIN [ContentDatabase].[dbo].[SpecialCategoryMapping] AS B ON B.[ContentID] = A.[ContentID] 
          INNER JOIN [ContentDatabase].[dbo].[SpecialCategory] as C ON B.[SpecialCategoryID] = C.[SpecialCategoryID] 
    WHERE A.Deleted = 0 AND A.PublishTypeId = 1 and A.[Name] like '%somepartialsearchstring%') as FinalTable
WHERE RowNum>=1 AND RowNum<=6

如果你需要选择类别名称对类别id在表[3DContent]，我认为没有必要表"[SpecialCategoryMapping]"。

SELECT * FROM 
        (SELECT A.[ContentId], A.[Name], A.[PublishTypeId] , A.[CategoryId], C.[Name] as CategoryName
          , ROW_NUMBER() OVER (ORDER BY A.ContentID DESC) AS RowNum 
          FROM [ContentDatabase].[dbo].[3DContent] AS A 
            JOIN [ContentDatabase].[dbo].[SpecialCategory] as C ON  A.[CategoryId] = C.[SpecialCategoryID] 
        WHERE A.Deleted = 0 AND A.PublishTypeId = 1 and A.[Name] like '%somepartialsearchstring%') as FinalTable
    WHERE RowNum>=1 AND RowNum<=6