Sql-server 2005 中的内连接问题

发布于 2024-11-15 10:12:20 字数 808 浏览 1 评论 0原文

   Select FileUpload.FileName AS FINAME, FileUpload.FilePath,MemberPersonalInformation.FirstName As SharedBy  from FileUpload 
   INNER JOIN 
   ContentManagement ON ContentManagement.FileId=FileUpload.FileId  
   INNER JOIN 
   MemberPersonalInformation ON MemberPersonalInformation.MemberId=ContentManagement.CreatedBy 
   INNER JOIN
    SubjectMaster ON ContentToIndividual.SubjectId=SubjectMaster.SubjectId 
    where 
   FileUpload.FileId in
    (Select FileId from ContentManagement where ContentId in
    (Select ContentId from ContentToIndividual where ShowToMemberId=12) 
    AND ContentManagement.ContentTypeId=1 and ContentManagement.SessionId=4)

当我执行此查询时，在最后一个 JOIN 中出现错误，提示 无法绑定多部分标识符“ContentToIndividual.SubjectId”。 但我确实在两个表。我不明白问题出在哪里。请帮助我。

原文

   Select FileUpload.FileName AS FINAME, FileUpload.FilePath,MemberPersonalInformation.FirstName As SharedBy  from FileUpload 
   INNER JOIN 
   ContentManagement ON ContentManagement.FileId=FileUpload.FileId  
   INNER JOIN 
   MemberPersonalInformation ON MemberPersonalInformation.MemberId=ContentManagement.CreatedBy 
   INNER JOIN
    SubjectMaster ON ContentToIndividual.SubjectId=SubjectMaster.SubjectId 
    where 
   FileUpload.FileId in
    (Select FileId from ContentManagement where ContentId in
    (Select ContentId from ContentToIndividual where ShowToMemberId=12) 
    AND ContentManagement.ContentTypeId=1 and ContentManagement.SessionId=4)

While I execute this query getting an error in the last JOIN saying The multi-part identifier "ContentToIndividual.SubjectId" could not be bound. But I do have SubjectId in both the tables.I couldn't understand whats the problem.Please help me out.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

久光 2024-11-22 10:12:20

您正在将 SubjectMaster 表连接到您之前未引用过的 ContentToIndividual 表。

您需要先加入 contentToIndvidual，然后才能在 SubjectMaster Join 中引用它。

例如

 Select FileUpload.FileName AS FINAME, 
        FileUpload.FilePath,MemberPersonalInformation.FirstName As SharedBy  
from FileUpload 
   INNER JOIN 
   ContentManagement ON ContentManagement.FileId=FileUpload.FileId  
   INNER JOIN 
   MemberPersonalInformation ON MemberPersonalInformation.MemberId=ContentManagement.CreatedBy 
    -- You need to add it in here   
    Inner Join ContentToIndividual on SomeColumn = AnotherColumn
   INNER JOIN
    SubjectMaster ON ContentToIndividual.SubjectId=SubjectMaster.SubjectId 
    where 
   FileUpload.FileId in
    (Select FileId from ContentManagement where ContentId in
    (Select ContentId from ContentToIndividual where ShowToMemberId=12) 
    AND ContentManagement.ContentTypeId=1 and ContentManagement.SessionId=4)

注意：即使您在子查询中查询 ContentToIndividual，如果它不是主选择查询的一部分，您也无法引用其中的列。

You are joining SubjectMaster table to the ContentToIndividual table which you have not previously referenced.

You need to Join on to contentToIndvidual before referencing it in your SubjectMaster Join.

e.g.

 Select FileUpload.FileName AS FINAME, 
        FileUpload.FilePath,MemberPersonalInformation.FirstName As SharedBy  
from FileUpload 
   INNER JOIN 
   ContentManagement ON ContentManagement.FileId=FileUpload.FileId  
   INNER JOIN 
   MemberPersonalInformation ON MemberPersonalInformation.MemberId=ContentManagement.CreatedBy 
    -- You need to add it in here   
    Inner Join ContentToIndividual on SomeColumn = AnotherColumn
   INNER JOIN
    SubjectMaster ON ContentToIndividual.SubjectId=SubjectMaster.SubjectId 
    where 
   FileUpload.FileId in
    (Select FileId from ContentManagement where ContentId in
    (Select ContentId from ContentToIndividual where ShowToMemberId=12) 
    AND ContentManagement.ContentTypeId=1 and ContentManagement.SessionId=4)

Note: Even though you are querying ContentToIndividual in a sub query you can't reference the columns in that if it is not part of the main select query.

回复收藏 0 原文

软糖 2024-11-22 10:12:20

您尚未在主 select 语句中加入 ContentToIndividual。您需要添加它或不引用它。

编辑：只是添加，您实际上不需要在主选择中添加 SubjectMaster 或 ContentToIndividual 连接，因为您没有从任一表中选择任何列- 请记住，子查询与主查询是分开的；您仅使用它来获取 FileId 列表。也可以优化语句的其余部分。

   Select FileUpload.FileName AS FINAME, FileUpload.FilePath,MemberPersonalInformation.FirstName As SharedBy  from FileUpload 
   INNER JOIN 
   ContentManagement ON ContentManagement.FileId=FileUpload.FileId  
   INNER JOIN 
   MemberPersonalInformation ON MemberPersonalInformation.MemberId=ContentManagement.CreatedBy 
   where 
   FileUpload.FileId in
    (Select FileId from ContentManagement where ContentId in
    (Select ContentId from ContentToIndividual where ShowToMemberId=12) 
    AND ContentManagement.ContentTypeId=1 and ContentManagement.SessionId=4)

编辑2：只是为了好玩，我认为这可能会简化一些事情，因为它摆脱了子查询，所以它应该更快......

SELECT      FileUpload.FileName AS FINAME, FileUpload.FilePath,MemberPersonalInformation.FirstName As SharedBy
FROM        FileUpload 
INNER JOIN  ContentManagement ON ContentManagement.FileId=FileUpload.FileId
            AND ContentManagement.ContentTypeId=1
            AND ContentManagement.SessionId=4
INNER JOIN  ContentToIndividual ON ContentToIndividual.ContentId = ContentManagement.ContentId -- Iguessed at this join
            AND ContentToIndividual.ShowToMemberId=12
INNER JOIN  MemberPersonalInformation ON MemberPersonalInformation.MemberId = ContentManagement.CreatedBy

You have not joined ContentToIndividual in your main select statement. You need to add it in or not reference it.

EDIT: Just to add, you actually don't need to add the SubjectMaster or the ContentToIndividual joins in the main select, since you aren't selecting any columns from either table - remember that the sub query is separate from the main query; you're only using it to get the list of FileIds. It may also be possible to optimise the rest of the statement too.

   Select FileUpload.FileName AS FINAME, FileUpload.FilePath,MemberPersonalInformation.FirstName As SharedBy  from FileUpload 
   INNER JOIN 
   ContentManagement ON ContentManagement.FileId=FileUpload.FileId  
   INNER JOIN 
   MemberPersonalInformation ON MemberPersonalInformation.MemberId=ContentManagement.CreatedBy 
   where 
   FileUpload.FileId in
    (Select FileId from ContentManagement where ContentId in
    (Select ContentId from ContentToIndividual where ShowToMemberId=12) 
    AND ContentManagement.ContentTypeId=1 and ContentManagement.SessionId=4)

EDIT 2: Just for fun, I think this might simplify things a little as it gets rid of the subqueries, so it should be faster...

SELECT      FileUpload.FileName AS FINAME, FileUpload.FilePath,MemberPersonalInformation.FirstName As SharedBy
FROM        FileUpload 
INNER JOIN  ContentManagement ON ContentManagement.FileId=FileUpload.FileId
            AND ContentManagement.ContentTypeId=1
            AND ContentManagement.SessionId=4
INNER JOIN  ContentToIndividual ON ContentToIndividual.ContentId = ContentManagement.ContentId -- Iguessed at this join
            AND ContentToIndividual.ShowToMemberId=12
INNER JOIN  MemberPersonalInformation ON MemberPersonalInformation.MemberId = ContentManagement.CreatedBy

回复收藏 0 原文

~没有更多了~