CODEWITHSUNDEEP
sql-server
Vasanth
Vasanth

Reputation: 128

How to nest SELECT queries with INNER JOIN

I have a nested SELECT query along with INNER JOIN. The idea is to fetch a Client's Username from a UserDetails table and TransactionId and Remarks from a LeaveDetails Table. I have a customer's UserId in both of these tables. I can fetch a Client's Username using Customer's UserId. The query goes like this:

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

and in the above query, dbo.UserDetails.[Username] should be fetched as below.

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

And I merged the queries as below which throws an error.

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

Please share your opinion to fix this issue. Thanks.

Upvotes: 0

Views: 254

Answers (2)

FutbolFan
FutbolFan

Reputation: 13733

If I understand your question correctly, this should be a simple join with a where clause like this:

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;

Upvotes: 2

xQbert
xQbert

Reputation: 35333

I believe you could simply this greatly if a Relationship actually exists between LeaveDetails and 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

Upvotes: 1

Related Questions