合并名称查询到第二查询匹配的ID号码

Question

这里有两个简单的查询：

SELECT EntityID, EntityName FROM EntityRelations.Entities 
WHERE EntityID IN (SELECT ModifiedByEntityID FROM EntityRelations.AssistanceFlags af WITH (nolock) 
    JOIN EntityRelations.Entities et WITH (nolock) ON af.EntityId = et.EntityId 
WHERE ModifiedDate BETWEEN '6/1/12 3:00 AM' 
         AND '8/1/12 2:59 AM') 

SELECT EntityName, Note, ModifiedByEntityID, ModifiedDate FROM EntityRelations.AssistanceFlags af WITH (nolock) 
    JOIN EntityRelations.Entities et WITH (nolock) ON af.EntityId = et.EntityId 
WHERE ModifiedDate BETWEEN '6/1/12 3:00 AM' 
         AND '8/1/12 2:59 AM' 
ORDER BY ModifiedDate ASC 

首先给出了名字的小桌子，我想滑入第二查询到ModifiedByEntityID场，出现名字而不是数字。

Answer 1

SELECT EntityName, Note, X.EntityName , ModifiedByEntityID, ModifiedDate FROM EntityRelations.AssistanceFlags af WITH (nolock) 
    JOIN EntityRelations.Entities et WITH (nolock) ON af.EntityId = et.EntityId 
LEFT OUTER JOIN (SELECT EntityID, EntityName FROM EntityRelations.Entities 
     WHERE EntityID IN (SELECT ModifiedByEntityID FROM EntityRelations.AssistanceFlags af WITH (nolock) 
     JOIN EntityRelations.Entities et WITH (nolock) ON af.EntityId = et.EntityId 
     WHERE ModifiedDate BETWEEN '6/1/12 3:00 AM' 
           AND '8/1/12 2:59 AM'))X ON X.EntityID = ModifiedByEntityID 
WHERE ModifiedDate BETWEEN '6/1/12 3:00 AM' 
         AND '8/1/12 2:59 AM' 
ORDER BY ModifiedDate ASC 

编辑由JerryB：我得到了一个模棱两可的实体名称的消息，所以我把它适应这一点，它的工作完美，感谢：

DECLARE @FROM DATETIME, @TO DATETIME 

    SET @FROM = '6/1/12 3:00 AM' 
    SET @TO = '8/1/12 3:00 AM' 

SELECT et.EntityName, Note, X.EntityName "CSR", ModifiedDate FROM EntityRelations.AssistanceFlags af WITH (nolock) 
    JOIN EntityRelations.Entities et WITH (nolock) ON af.EntityId = et.EntityId 
LEFT OUTER JOIN (SELECT EntityID, EntityName FROM EntityRelations.Entities 
     WHERE EntityID IN (SELECT ModifiedByEntityID FROM EntityRelations.AssistanceFlags af WITH (nolock) 
     JOIN EntityRelations.Entities et WITH (nolock) ON af.EntityId = et.EntityId 
     WHERE ModifiedDate BETWEEN @FROM AND @TO))X ON X.EntityID = af.ModifiedByEntityID 
WHERE ModifiedDate BETWEEN @FROM AND @TO 
ORDER BY ModifiedDate ASC 

Answer 2

你可以JOIN两个查询到一个：

SELECT EntityName, Note, ModifiedByEntityID, e.EntityName, ModifiedDate 
FROM EntityRelations.AssistanceFlags af WITH (nolock) 
JOIN EntityRelations.Entities et WITH (nolock) 
    ON af.EntityId = et.EntityId 
LEFT JOIN 
(
    SELECT EntityID, EntityName 
    FROM EntityRelations.Entities 
    WHERE EntityID IN (SELECT ModifiedByEntityID 
        FROM EntityRelations.AssistanceFlags af WITH (nolock) 
        JOIN EntityRelations.Entities et WITH (nolock) 
         ON af.EntityId = et.EntityId 
        WHERE ModifiedDate BETWEEN '6/1/12 3:00 AM' AND '8/1/12 2:59 AM') 
) e 
    ON ModifiedByEntityID = e.EntityID 
WHERE ModifiedDate BETWEEN '6/1/12 3:00 AM' AND '8/1/12 2:59 AM' 
ORDER BY ModifiedDate ASC 

Answer 3

愚蠢的做法是加入你的两个查询并获得你想要的字段。

select a.EntityName,a.Note,b.EntityID,a.ModifiedDate 
from 
(
SELECT EntityID, EntityName FROM EntityRelations.Entities 
WHERE EntityID IN (SELECT ModifiedByEntityID FROM EntityRelations.AssistanceFlags af WITH (nolock) 
    JOIN EntityRelations.Entities et WITH (nolock) ON af.EntityId = et.EntityId 
WHERE ModifiedDate BETWEEN '6/1/12 3:00 AM' 
         AND '8/1/12 2:59 AM') 
) as b 
inner join 
(
SELECT EntityName, Note, ModifiedByEntityID, ModifiedDate FROM EntityRelations.AssistanceFlags af WITH (nolock) 
    JOIN EntityRelations.Entities et WITH (nolock) ON af.EntityId = et.EntityId 
WHERE ModifiedDate BETWEEN '6/1/12 3:00 AM' 
         AND '8/1/12 2:59 AM' 

) as a 
on b.EntityID = a.ModifiedByEntityID 
ORDER BY a. ModifiedDate ASC 

当然，这将是一个缓慢的查询，但如果你在小桌子上工作会有帮助。