SQL Joins question - SQL Server Forums

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)

SQL Joins question

Author

Topic

CSharpNewbie
Starting Member

39 Posts

Posted - 2009-03-02 : 19:48:55

Hi,
I have a table called tblIssueTicket
tblIssueTicket contains the fields: TicketID, TicketRequesterID, ApprovalManagerID, RequestDate, ApprovalDate, TicketStatus

There is another table called tblEmployeeProfile.
tblEmployeeProfile contains fields EmployeeID, EmployeeFirstName, EmployeeLastName

I need to display the following records:
TicketID, TicketRequestFullName, ApprovalManagerFullName, RequestDate, ApprovalDate, TicketStatus

I am having problems figuring out the query to return the fullname of the TicketRequester & fullname of ApprovalManager fields.

My query so far looks like this:

Select it.TicketID,
ep.Firstname + ' ' + ep.EmployeeLastName AS TicketRequestorFullName,
it.RequestDate, it.ApprovalDate, it.TicketStatus
FROM tblIssueTicket it, tblEmployeeProfile ep
WHERE ep.EmployeeID = it.TicketRequesterID

Any advice would greatly appreciated.

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-03-02 : 20:27:51

[code]select
it.TickerID,
ep1.EmployeeFirstName + ' ' + ep1.EmployeeLastName as TicketRequesterFullName,
ep2.EmployeeFirstName + ' ' + ep2.EmployeeLastName as ApprovalManagerFullName,
it.RequestData,
it.ApprovalDate,
it.TicketStatus
from
tblIssueTicker it
inner join tblEmployeeProfile ep1 on ep1.EmployeeId = it.TicketRequesterID
inner join tblEmployeeProfile ep2 on ep2.EmployeeId = it.ApprovalManagerId[/code]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-02 : 23:36:03

[code]
Just small modification to sunita code,
select
it.TickerID,
ISNULL(ep1.EmployeeFirstName+' ','')+ ISNULL(ep1.EmployeeLastName,'')as TicketRequesterFullName,
ISNULL(ep2.EmployeeFirstName+' ','')+ ISNULL(ep2.EmployeeLastName,'')as ApprovalManagerFullName,
-- COALESCE(ep1.EmployeeFirstName+' ','')+ COALESCE(ep1.EmployeeLastName,'')as TicketRequesterFullName,
-- COALESCE(ep2.EmployeeFirstName+' ','')+ COALESCE(ep2.EmployeeLastName,'')as ApprovalManagerFullName,
it.RequestData,
it.ApprovalDate,
it.TicketStatus
from
tblIssueTicker it
inner join tblEmployeeProfile ep1 on ep1.EmployeeId = it.TicketRequesterID
inner join tblEmployeeProfile ep2 on ep2.EmployeeId = it.ApprovalManagerId
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources