Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2011
    Posts
    6

    Unanswered: Need help to solve a T-sql query.

    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)

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 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).
    Code:
    SELECT tblTransfer.TransferID, 
    	tblTransfer.AssetID, 
    	tblAssets.name as AssetName, 
    	tblTransfer.TransferredQty, 
    	tblTransfer.TransferNoteNo, 
    	tblTransfer.TransferedToProjID, 
    	ProjTo.Name AS TransferedToProject,
    	tblTransfer.TransferredFromProjID,
    	ProjFrom.Name AS TransferedFromProject,
    	tblTransfer.TransferedToDivision, 
    	tblTransfer.TransferredFromDiv, 
    	tblTransfer.TransferDate, 
    	tblTransfer.TransferredBy, 
    	tblUsers.name as TransferredByName
    FROM tblTransfer
    	INNER JOIN tblUsers ON
    		 tblTransfer.TransferredBy = tblUsers.UserID
    	INNER JOIN tblAssets ON
    		tblTransfer.AssetID = tblAssets.AssetID
    	LEFT OUTER JOIN tblProject AS ProjTo ON
    		tblTransfer.TransferedToProjID = ProjTo.ProjectID
    	LEFT OUTER JOIN tblProject AS ProjFrom ON
    		tblTransfer.TransferedToProjID = ProjFrom.ProjectID
    WHERE tblTransfer.AssetID = 4
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Nov 2011
    Posts
    6

    Thumbs up Solved... Thats Great...

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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •