Please help to solve the query :
I have following tables :
tblTransfer -> (TransferID-PK, AssetID-FK)
tblAssets -> (AssetID - PK)
tblProject -> (ProjectID - PK)
tblUsers -> (UserID - PK)

tblTransfer columns :
TransferID, int
AssetID, int
TransferredQty, int
TransferNoteNo, nvarchar(50)
TransferedToProjID, int
TransferredFromProjID, int
TransferedToDivision, nvarchar(50)
TransferredFromDiv, nvarchar(50)
TransferDate, datetime
TransferredBy, int

tblProject Columns :
ProjectID, int
Name, nvarchar(50)
Location, nvarchar(50)
Manager, nvarchar(50)

i want the results as :
TransferID,AssetID,TransferredQty,TransferNoteNo,T ransferredToProjName,TransferredFromProjName,Trans ferredToDiv,TransferredFromDiv,TransferDate,Transf erredBy

The problem is to get the ProjectName that is TransferredToProjID and TransferredFromProjName. How to achieve this result.
My original query is following excluding TransferredFromProjID :
SELECT tblTransfer.TransferID, tblTransfer.AssetID, tblTransfer.TransferredQty, tblTransfer.TransferNoteNo, tblTransfer.TransferedToProjID, tblTransfer.TransferedToDivision, tblTransfer.TransferDate, tblTransfer.TransferredBy, tblProject.Name, tblProject.ProjectID
FROM tblUsers RIGHT OUTER JOIN
tblAssets RIGHT OUTER JOIN
tblProject RIGHT OUTER JOIN
tblTransfer ON tblProject.ProjectID = tblTransfer.TransferedToProjID ON tblAssets.AssetID = tblTransfer.AssetID ON tblUsers.UserID = tblTransfer.TransferredBy
WHERE (tblTransfer.AssetID = 4)

I am assuming that a transfer can only exist when someone does it (TransferredBy IS NOT NULL), and when an asset is transferred (AssetID IS NOT NULL).

Hey thanks for your quick reply... Thank you so much........ Its working great..