How do I use multiple IDs from a table with an INNER JOIN using SQL?

I have a list of SiteUsers in one table and another table has columns with different types of owners (ID) for the record. For example, the SiteUserID in the SiteUsers table will be used for the SalesRepID, the StaffingManagerID, and RecruiterID in the Fill table. Of course, the SiteUserID is different for each of the values in the Fill table开发者_如何学Go.

I'd like to return the name of the SiteUser for each ID column in the Fill Table. How do I properly construct a JOIN statement to do this?

I'm guessing this is done through INNER JOIN, but I'm not sure.

My current select statement already has an INNER JOIN as I'm pulling the name of the FillType from another table. I'm using this in an asp.net application.

I'm not sure if this is even possible. Any help is appreciated.

Since each of the IDs in the Fills table allows null, you probably want to LEFT JOIN to the SiteUsers table like so:

SELECT f.FillID, s1.SiteUserLastName 'SalesRep', s2.SiteUserLastName 'StaffingManager', s3.SiteUserLastName 'Recruiter'
FROM Fills f
LEFT JOIN SiteUsers s1 on f.SalesRepID = s1.SiteUserID
LEFT JOIN SiteUsers s2 on f.StaffingManagerID = s2.SiteUserID    
LEFT JOIN SiteUsers s3 on f.RecruiterID = s3.SiteUserID

You can always UNPIVOT the results like so:

SELECT
DISTINCT
    unpvt.FillID
    ,unpvt.RepID
    ,unpvt.RepType
    ,s.SiteUserFirstName
    ,s.SiteUserLastName
FROM  
    (SELECT
        FillID
       ,SalesRepID 
       ,StaffingManagerID 
       ,RecruiterID
    FROM Fills 
    ) f
       UNPIVOT
   (RepID FOR RepType  IN 
   (SalesRepID, StaffingManagerID,RecruiterID)
   ) AS unpvt
      JOIN SiteUsers AS s on unpvt.RepID = s.SiteUserID`

Obviously you can play with exact output (such as substituting the RepType for a different value with a CASE statement or whatnot.

My question is: why the piss-poor design? Instead of having three IDs in the Fills table, you should have a junction table between SiteUsers and Fills to allow many-to-many relationships. IF it were designed with a junction table, you'd never have had to ask this question.

You will have to join the Fill table with the SiteUsers table multiple times, one for each xxxID column in the Fills for which you want the SiteUser name and combine the results using an union as below:

 select a.SiteUserId, a.SiteUserFirstName, a.SiteUserLastName
       from dbo.SiteUsers a
         inner join dbo.Fills b on b.SalesRepId = a.SiteUserId
  UNION  
 select a.SiteUserId, a.SiteUserFirstName, a.SiteUserLastName
       from dbo.SiteUsers a
         inner join dbo.Fills b on b.StaffingManagerId = a.SiteUserId
  UNION  
 select a.SiteUserId, a.SiteUserFirstName, a.SiteUserLastName
       from dbo.SiteUsers a
         inner join dbo.Fills b on b.RecruiterId = a.SiteUserId