CODEWITHSUNDEEP
sqlsql-serverreportingmultiple-columns
David Flynn
David Flynn

Reputation: 105

Getting two columns referring to one table from the same row in a table?

So in this database, I have two tables I am interested in. One is a "Details" table and the other is a "Staff" table.

In the details table, I have many columns but the two I'm interested in are:

The values in these columns are references to the StaffID column in the Staff table. The Staff table is made of these columns:

I'm trying to do a report where I can show details of a row along with the worker and manager involved with that specific detail (row). In this report, I want to display both the worker's name, and the manager's name. Problem is, I can't get it to work. It will only display results where the Worker and Manager are referring to the same person. Here is my query:

SELECT  Staff.LastName + ', ' + Staff.FirstName AS [Worker], 
        Staff.LastName + ', ' + Staff.FirstName AS [Manager], 
        Details.SiteName AS [Site Name], 
        Details.Description AS [Description], 
        Details.Action AS [Action],
FROM  Details 
INNER JOIN Staff 
   ON Details.Worker= Staff.StaffID AND Details.Manager = Staff.StaffID

So I'm not quite sure what to do about this. I'm very confused. Help will be really appreciated! Thanks!

Upvotes: 0

Views: 3707

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

You have to join with Staff table twice: one time to get the worker details and a second time to get the manager details:

SELECT W.LastName + ', ' + W.FirstName AS [Worker], 
       M.LastName + ', ' + M.FirstName AS [Manager], 
       Details.SiteName AS [Site Name], 
       Details.Description AS [Description], 
       Details.Action AS [Action],
FROM Details 
INNER JOIN Staff AS W ON Details.Worker = W.StaffID 
INNER JOIN Staff AS M ON Details.Manager = M.StaffID

Upvotes: 3

Related Questions