INNER JOIN two columns to one using MSSQL database-CodePudding

Here's what my 2 tables look like

Table A
---------
userID.   |.   adminID

Table B
---------
IDs       |    fName

In Table A, both the userID and adminID are found in table B. I'm trying to return the Full name for each.

This is the SQL I am currently using

SELECT fName FROM tableB INNER JOIN tableA ON tableA.userID=tableB.IDs.

This is where i'm stuck. This only returns 1 column. I would like it to return each fName in their own column. Any help is appreciated. Thank you.

CodePudding user response：

Have you tried UNION?

SELECT fName FROM tableB INNER JOIN tableA ON tableA.userID=tableB.IDs
UNION
SELECT fName FROM tableB INNER JOIN tableA ON tableA.adminID=tableB.IDs

CodePudding user response：

Or, if this is more of an employee and their admin point-of-contact and you want it on each row, such that different employees could have different admin contacts..

select
      a.UserID,
      u.fname UserFirstName,
      au.fname AdminFirstName
   from
      tableA a
         join tableB u
            on a.userID = u.IDs
         join tableB au
            on a.adminID = au.IDs

Now, if the admin ID is not always available, just change the second join to a LEFT JOIN. Notice different alias references of the same table to get context of each first name.