sql server语言 - 如何用INNER JOIN嵌套SELECT查询

Question

我有一个嵌套的SELECT查询和INNER JOIN。这个想法是从UserDetails表中获取客户端的Username，从LeaveDetails表中获取TransactionId和comments。我在这两个表中都有一个客户的UserId。我可以使用客户的UserId获取客户的用户名。查询是这样的:

SELECT 
dbo.UserDetails.[Username], dbo.LeaveDetails.[TransId], dbo.LeaveDetails.Remarks 
FROM dbo.UserDetails 
INNER JOIN 
dbo.LeaveDetails 
  ON dbo.LeaveDetails.UserId=@UID 
  AND dbo.LeaveDetails.[TransId] = @TransId

，在上面的查询中，dbo.UserDetails。[Username]应按如下方式获取

SELECT 
dbo.UserDetails.[ClientUserId] from dbo.UserDetails 
WHERE UserId = @UID

和我合并查询如下抛出一个错误。

SELECT 
dbo.UserDetails.[Username] 
WHERE 
UserId = (SELECT dbo.UserDetails.[ClientUserId] 
          from dbo.UserDetails 
          WHERE UserId = @UID), 
dbo.LeaveDetails.[TransId], dbo.LeaveDetails.Remarks 
FROM dbo.UserDetails 
INNER JOIN dbo.LeaveDetails 
  ON dbo.LeaveDetails.UserId=@UID 
  AND dbo.LeaveDetails.[TransId] = @TransId

请分享你的意见来解决这个问题。谢谢。

Answer 1

如果我正确理解你的问题，这应该是一个简单的join与where子句，像这样:

SELECT ud.Username
    ,ld.[TransId]
    ,ld.Remarks
FROM dbo.UserDetails ud
INNER JOIN dbo.LeaveDetails ld ON ld.UserId = ud.UserId
WHERE ud.ClientUserId IN (
        SELECT ud1.ClientUserId
        FROM dbo.UserDetails ud1
        WHERE ud1.UserId = @UID
        )
    AND ld.[TransId] = @TransId;

Answer 2

如果在LeaveDetails和userDetails之间确实存在关系，我相信您可以简单地做到这一点。

SELECT UD.[Username], LD.[TransId], LD.Remarks 
FROM dbo.UserDetails  UD
INNER JOIN dbo.LeaveDetails LD
 ON UD.PKField = LD.FKField  <-- Change this to be your actual relationship
--Maybe ON UD.ClientUserID = LD.ClientUserID --Instead of above
WHERE LD.ClientUserId=@UID 
  AND LD.[TransId] = @TransId