Need a Help on SQL Server 2005 T-SQL Query.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need a Help on SQL Server 2005 T-SQL Query.

Author

Topic

tonsetony
Starting Member

4 Posts

Posted - 2011-12-21 : 04:37:46

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,TransferredToProjName,TransferredFromProjName,TransferredToDiv,TransferredFromDiv,TransferDate,TransferredBy

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)

Tony Dsouza
---------------

MartinCroft
Starting Member

4 Posts

Posted - 2011-12-21 : 07:07:28

Can you post the schema of the tables ( create table scripts, FK) and the alais of the columns you want the data from. it will make it easier to ensure the code runs against your tables

tonsetony
Starting Member

4 Posts

Posted - 2011-12-21 : 07:21:31

quote:
Originally posted by MartinCroft

Can you post the schema of the tables ( create table scripts, FK) and the alais of the columns you want the data from. it will make it easier to ensure the code runs against your tables

Thanks for your quick reply... I got the answer from another forum...
this is what i wanted :


SELECT     tblTransfer.TransferID, tblTransfer.AssetID, tblTransfer.TransferredQty, tblTransfer.TransferNoteNo, tblTransfer.TransferredToProjID, 
                      ProjTo.Name AS TransferredToProject, tblTransfer.TransferredFromProjID, ProjFrom.Name AS TransferredFromProject, tblTransfer.TransferredToDivision, 
                      tblTransfer.TransferredFromDiv, tblTransfer.TransferDate, tblTransfer.TransferredBy, tblUsers.UserName AS TransferredByName
FROM         tblTransfer LEFT OUTER JOIN
                      tblUsers ON tblTransfer.TransferredBy = tblUsers.UserID LEFT OUTER JOIN
                      tblAssets ON tblTransfer.AssetID = tblAssets.AssetID LEFT OUTER JOIN
                      tblProject AS ProjTo ON tblTransfer.TransferredToProjID = ProjTo.ProjectID LEFT OUTER JOIN
                      tblProject AS ProjFrom ON tblTransfer.TransferredFromProjID = ProjFrom.ProjectID
WHERE     (tblTransfer.AssetID = 585)

Once again thank you.....

Tony Dsouza
---------------

sureshkk
Starting Member

21 Posts

Posted - 2011-12-21 : 07:31:07

Run the following query to get the result,If u want Division name join this query with Division table twice by taking to aliases

SELECT tblTransfer.TransferID,
tblTransfer.AssetID,
tblTransfer.TransferredQty,
tblTransfer.TransferNoteNo,
p2.Name TransferredToProjName
p1.Name TransferredFromProjName,
tblTransfer.TransferredToDiv,
tblTransfer.TransferredFromDiv,
tblTransfer.TransferDate,
tblTransfer.TransferredBy,
tblProject.Name,
tblProject.ProjectID
FROM tblTransfer
INNER JOIN tblProject p1
ON tblProject.ProjectID = tblTransfer.TransferredFromProjID
INNER JOIN tblProject p2
ON tblProject.ProjectID = tblTransfer.TransferedToProjID
INNER JOIN
WHERE (tblTransfer.AssetID = 4)

sureshkk
Starting Member

21 Posts

Posted - 2011-12-21 : 07:33:16

Run the following query to get the result,If u want Division name join this query with Division table twice by taking two aliases

--remove last inner join

SELECT tblTransfer.TransferID,
tblTransfer.AssetID,
tblTransfer.TransferredQty,
tblTransfer.TransferNoteNo,
p2.Name TransferredToProjName
p1.Name TransferredFromProjName,
tblTransfer.TransferredToDiv,
tblTransfer.TransferredFromDiv,
tblTransfer.TransferDate,
tblTransfer.TransferredBy,
tblProject.Name,
tblProject.ProjectID
FROM tblTransfer
INNER JOIN tblProject p1
ON tblProject.ProjectID = tblTransfer.TransferredFromProjID
INNER JOIN tblProject p2
ON tblProject.ProjectID = tblTransfer.TransferedToProjID
---INNER JOIN
WHERE (tblTransfer.AssetID = 4)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources