Need to get multiple value from a table in the left join

Question

I have a table having data as below.

Say I have two versions of the project and I need to migrate data from older version to a new version.

Let's say tblFolders in version1

+----+------------+--------------+--------------+
| id | FolderName | CreatedBy    | ModifiedBy   |
+----+------------+--------------+--------------+
|  1 | SIMPLE     | 5            | 6            |
|  2 | SIMPLE1    | 8            | 1            |
+----+------------+--------------+--------------+

And another table having userid of both versions.

Let's say its tblUsersMapping

+----+----------------+-------------------+
| id | Version1UserID | Version2UserID    |
+----+----------------+-------------------+
|  1 | 1              | 500               |
|  2 | 2              | 465               |
|  3 | 3              | 12                |
|  4 | 4              | 85                |
|  5 | 5              | 321               |
|  6 | 6              | 21                |
|  7 | 7              | 44                |
|  8 | 8              | 884               |
+----+----------------+-------------------+

Now I need to transfer data from version 1 to version 2. When I transferring data, CreatedBy and Modifiedby ids should by of the new version.

So though I have data as below

|  1 | SIMPLE     | 5            | 6            |

It should be transferred as below

|  1 | SIMPLE     | 321          | 21           |

For that, I have added a join so far between these two tables as below.

SELECT id, 
   foldername, 
   B.version2userid AS CreatedBy
FROM   tblfolders A WITH(nolock) 
       LEFT JOIN tblusersmapping B WITH(nolock) 
              ON A.createdby = B.version1userid

This would give me a proper result for column CreatedBy.

But how can I get userid from tblUsersMapping for ModifiedBy column? Doing below will not work and will give NULL for both the columns.

SELECT    id, 
      foldername, 
      b.version2userid AS createdby, 
      b.version2userid AS modifiedby 
FROM      tblfolders A WITH(nolock) 
LEFT JOIN tblusersmapping B WITH(nolock) 
ON        a.createdby = b.version1userid, 
          a.modifiedby = b.version1userid

One way is I can add another join with tblusersmapping table. But its not a good idea because tables can have a huge data and another join will affect the performance of the query.

My question is how can I get Version1UserID and Version2UserID from mapping table based on createdby and modifiedby columns?

Answer 1

You can use multiple select which may help you.

SELECT id, 
foldername, 
(SELECT version2userid from tblUsersMapping where Version1UserID=tblfolders.CreatedBy) AS CreatedBy,
(SELECT version2userid from tblUsersMapping where Version1UserID=tblfolders.ModifiedBy) AS ModifiedBy
FROM   tblfolders

Answer 2

If you want to populate both the column where each column joins to to a different row, in that case you have to join the same table twice like following. You can't get it with a single table join the way you are expecting.

SELECT id, 
   foldername, 
   B.version2userid AS CreatedBy
   C.Version2UserID    AS ModifiedBy
FROM   tblfolders A WITH(nolock) 
       LEFT JOIN tblusersmapping B WITH(nolock) 
              ON A.createdby = B.version1userid
        LEFT JOIN tblusersmapping C WITH(nolock) 
              ON A.ModifiedBy    = C.version1userid

Answer 3

Try this, it will work across all sample data,

select tf.id,tf.FolderName
,oa.Version2UserID as CreatedBy
,oa1.Version2UserID as ModifiedBy
from @tblFolders tf
outer apply(select top 1 Version2UserID 
from @tblUsersMapping tu 
where tu.Version1UserID= tf.CreatedBy order by id desc)oa
outer apply(select top 1 Version2UserID 
from @tblUsersMapping tu 
where tu.Version1UserID= tf.ModifiedBy order by id desc)oa1