Home > Blockchain >  INNER JOIN two columns to one using MSSQL database
INNER JOIN two columns to one using MSSQL database

Time:01-04

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.

  • Related