using Select Statement to get Same columns repeat

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

SQL Server 2005 Forums

Transact-SQL (2005)

using Select Statement to get Same columns repeat

Author

Topic

Sudhindra
Starting Member

13 Posts

Posted - 2008-12-30 : 01:08:25

Hi, I have a table which has a AssignedtoID & AssignedbyID which are Pks, Other 3 tables have Depeartment,designation & Employee table contains name, designationName, DeparmentName for these Pks.
Now I need to display in gridview like this:

ASignedBy(name) Department Designation AssignedTo(name) Department Designation

I had a query writen like this & need to miodify further like this:-

CREATE PROCEDURE EmpSelectVacApp_ByReqID --12
(
@ReqID int
)
AS
BEGIN
SELECT dbo.VacationApprovalDt.AppReqID, dbo.VacationApprovalDt.ReqID, dbo.VacationApprovalDt.AssignTo, dbo.MstEmployee.EmpCode,
(dbo.MstEmployee.FirstName +' '+ ISNULL(dbo.MstEmployee.MiddleName,' ') +' ' + dbo.MstEmployee.LastName) as FirstName, dbo.MstEmployee.DesigId, dbo.MstDesignation.DesigCode, dbo.MstDesignation.DesigName, dbo.MstEmployee.DeptId,

dbo.MstDepartment.DeptCode, dbo.MstDepartment.DeptName,dbo.VacationApprovalDt.Status,dbo.VacationApprovalDt.Remarks,dbo.VacationApprovalDt.ModifiedBy

FROM dbo.MstDepartment INNER JOIN
dbo.MstEmployee ON dbo.MstDepartment.DeptId = dbo.MstEmployee.DeptId LEFT OUTER JOIN
dbo.MstDesignation ON dbo.MstEmployee.DesigId = dbo.MstDesignation.DesigId Right OUTER JOIN
dbo.VacationApprovalDt ON dbo.MstEmployee.EmpId = dbo.VacationApprovalDt.AssignTo
where dbo.VacationApprovalDt.ReqID=@ReqID
END

Help would be really appreciated....
Thanks in advance

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-30 : 01:09:29

What tables do those fields come from?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Sudhindra
Starting Member

13 Posts

Posted - 2008-12-30 : 01:18:01

VacationApprovalDt has AssignedToID, & AssignedByID is Modifiedby Column in that same table,
ASsignedTo is Actually the Empcode & modifiedby is also the Employee code which is in MstEmployee.
mstDesignation contains MstEmployee has only DesignationId whch is Pk of mstDesignation Table, MStEmployee has DepartmentID which is Pk of MstDepartment. So on that I need to join tables & get DesignationName (MstDesignation Table) & also DepartmentName (MstDepartment table),
So my grid will look like this:

AssignedBy Designation Department AssignedTo Designation Department
(EmpCode) (His DesignationName) (His DepartmentName) (EmpCode) (His DesignationName) (His DepartmentName)

MStEmployee:
1)EmpCode
2)Name

MstDesignation:
1)DsgnCode
2)name

MStDepartment:
1)DepCde
2)Name

VacationAppvlDt:
1)AssignedTo
2)ModifidedBy(AssignedBy)

Sudhindra
Starting Member

13 Posts

Posted - 2008-12-30 : 01:19:54

ohh Also VacationAppvlDt table has another Column
1)ApprvlID(pk)
2)requestID(ReqID)
3)AssignedTo
4)ModifidedBy(AssignedBy)
& i am going to use RequestID to query to get the Modifiedby & Assigned to IDs & all other details

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 01:25:41

[code]
CREATE PROCEDURE EmpSelectVacApp_ByReqID --12
(
@ReqID int
)
AS
BEGIN
SELECT v.ModifiedBy AS AssignBy,
desg1.DesignationName,
dept1.DepartmentName,
v.AssignedTo,
desg2.DesignationName,
dept2.DepartmentName
FROM VacationApprovalDt v
JOIN MStEmployee e1
ON e1.EmpCode=v.ModifiedBy
JOIN MStEmployee e2
ON e2.EmpCode=v.AssignedTo
JOIN MstDesignation desg1
ON desg1.DesignationId=e1.DesignationId
JOIN MstDepartment dept1
ON dept1.DepartmentID =e1.DepartmentID
JOIN MstDesignation desg2
ON desg1.DesignationId=e2.DesignationId
JOIN MstDepartment dept2
ON dept2.DepartmentID =e2.DepartmentID
WHERE v.RequestID=@ReqID
END
[/code]

Sudhindra
Starting Member

13 Posts

Posted - 2008-12-30 : 01:39:50

Hey visakh16, thanks the query works with a LEFT Outer join. I am getting the result.Thnak you so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 01:41:58

welcome

Sudhindra
Starting Member

13 Posts

Posted - 2008-12-30 : 01:44:12

The only Problem is the 2nd last column in the result thats DesignationName is coming as NULL when i use left join. Without including "Left join" the query doesnt produce any result.

Sudhindra
Starting Member

13 Posts

Posted - 2008-12-30 : 01:57:32

Ohh SOrry I got it Thankyou, spellingmistake was causing it. Cant thank you enough

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 01:58:02

quote:
Originally posted by Sudhindra

The only Problem is the 2nd last column in the result thats DesignationName is coming as NULL when i use left join. Without including "Left join" the query doesnt produce any result.

that means you dont have any records in MstDesignation table that matches the DesignationId values in MStEmployee table corresponding to AssignedTo emp code value

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources