Sql-server – Need help with a query for a helpdesk application

sql serversql-server-2012

I have a helpdesk application I'm modifying.

I need to pull data into an ASP.Net DataGrid.

In the problems table, there is a field for employeeID, which is the employee id of the person entering the ticket. There is also a rep field which holds the employee id of the rep handling the ticket.

I'm trying get the employee NAME for both the person entering the ticket and the rep handling the ticket from a users table.

Here are the relevant fields…

Problem Table:

pID
employeeID
sLocation
sRoom
pRep


Users Table:

fName
lName
empID

Here's an example of a query I'm using to pull the problems associated with just one rep:

SELECT p.pID, u.fName + ' ' + u.lName as FullName, p.sLocation, p.sRoom, p.pStatus, p.pTitle
FROM Problems p INNER JOIN tblUsers u ON p.employeeID = u.empID
WHERE p.pRep=Session("EmpID")

If I want to pull all the problems and list the rep who is handling the issue, what would that query be?

Would it look something like this (thought I feel sure there's more to that join statement)

SELECT p.pID, u.fName + ' ' + u.lName as EmpFullName, p.sLocation, p.sRoom, p.pStatus, p.pTitle, u.fName + ' ' + u.lName as RepFullName
FROM Problems p INNER JOIN tblUsers u ON p.employeeID = u.empID
WHERE p.pStatus='OPEN'

Best Answer

Simply add another join to the tblUsers table, to get the name of the other employee.

SELECT p.pID
    , EmpFullName = uEmp.fName + ' ' + uEmp.lName
    , p.sLocation
    , p.sRoom
    , p.pStatus
    , p.pTitle
    , RepFullName = uRep.fName + ' ' + uRep.lName
FROM dbo.Problems p 
    INNER JOIN dbo.tblUsers uEmp ON p.employeeID = uEmp.empID
    INNER JOIN dbo.tblUsers uRep ON p.repID = uRep.empID
WHERE p.pStatus='OPEN';

As you can see, there are two instances of the dbo.tblUsers table referenced in the FROM clause, each with a different alias. This allows you to return separate rows from tblUsers, one for the EmployeeID and one for the RepID

You'll likely need to add to the WHERE clause to pulls rows from the Problem table for a particular EmployeeID or a particular RepID depending on how you want to display results. This might look something like:

WHERE p.pStatus='OPEN'
    AND p.empID = 42;

Notice, I've prefaced all tables in the FROM clause with the name of the schema where those tables reside. This is a great habit to have since it will prevent issues down the road if you decide to have more than a single schema.

I also listed each column on its own row, and moved the column aliases in front of the actual column name in the SELECT clause; this makes it easier to find the particular column later when debugging.

You should also get in the habit of appending a semi-colon to the end of all T-SQL statements, since that can mitigate problems with the WITH CTE construct, among others.

Related Question