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?

Vivekanand Panda · Answer 1 · 2019-01-22T06:24:24.803

3

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

edited Jan 22 '19 at 06:24

answered Jan 22 '19 at 06:11

Vivekanand Panda

832
12
23

1

This is a bad idea because it will slow when table have many records. – gongsun Jan 22 '19 at 06:22
Yes you are right, in performance sense it will little bit slow. – Vivekanand Panda Jan 22 '19 at 06:25

score 2 · Answer 2 · answered Jan 22 '19 at 06:08

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

answered Jan 22 '19 at 06:08

PSK

17,547
5
32
43

Thank you for your answer. Do you have any other solution by which I do not need to and another join? Because as mention in the question, there might be over crore data in the table and a single join is already a big deal for me. – Maharshi Jan 22 '19 at 06:12
2

In your current scenario you have to use it twice and this is very common. If your tables are indexed properly you should not get any performance issues. – PSK Jan 22 '19 at 06:21

score 2 · Answer 3 · answered Jan 22 '19 at 07:41

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

Shushil Bohara · Answer 4 · 2019-01-22T09:37:34.807

You can use UDF to return modifiedby and INNER JOIN instead of LEFT JOIN (if requirement meets) as below. I think it will help in the preformance

CREATE TABLE tblFolders (id INT, folderName VARCHAR(20), createdBy INT, modifiedBy INT)
INSERT INTO tblFolders VALUES
(1,'SIMPLE',     5,6),
(2,'SIMPLE1',    8,1)

CREATE TABLE tblUsersMapping(id INT, Version1UserID INT, Version2UserID INT)
INSERT INTO tblUsersMapping VALUES
(1,1,500),
(2,2,465),
(3,3,12),
(4,4,85),
(5,5,321),
(6,6,21),
(7,7,44),
(8,8,884)


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

--Function
IF OBJECT_ID(N'dbo.FNAReturnModifiedBy', N'FN') IS NOT NULL
DROP FUNCTION dbo.FNAReturnModifiedBy
 GO 

CREATE FUNCTION dbo.FNAReturnModifiedBy(@updated_by INT)
RETURNS INT AS  
BEGIN 
    DECLARE @updateUserID INT
    SELECT @updateUserID = Version2UserID 
    FROM tblusersmapping WHERE Version1UserID = @updated_by

    RETURN @updateUserID
END

OUTPUT:

id  foldername  createdby   modifiedby
1   SIMPLE      321         21
2   SIMPLE1     884         500

It gives result really faster. There are few mismatches in the count comparing to the left join so I will check that and also need to check how to fit this query in my code since I have whole system dynamic and queries being created dynamic based on the structure of the class. Thanks for the answer. — Maharshi, Jan 22 '19 at 10:26

score 2 · Answer 5 · edited Jun 20 '20 at 09:12

Note :

I did not know about how to find the query performance. I wrote only for your expected output.
I am using SQL Server 2012.
I did not use more than one Join.
Query did JOIN, GROUP BY, ROW_NUMBER (), CASE instead of two LEFT JOIN

Input :

create table ##ver (id int,  FolderName varchar (10), CreatedBy     int, ModifiedBy   int)

insert into ##ver values 
 (1,'SIMPLE',5,6)
,(2,'SIMPLE1',8,1)
,(3,'File',7, 5)

select * from ##ver

create table ##veruser (id int,  Version1UserID  int, Version2UserID    int)

insert into ##veruser values
 (1 , 1 , 500)
,(2 , 2 , 465)
,(3 , 3 , 12 )
,(4 , 4 , 85 )
,(5 , 5 , 321)
,(6 , 6 , 21 )
,(7 , 7 , 44 )
,(8 , 8 , 884)

select * from ##veruser

Query :

select 
id, FolderName 
, max (case when rn = 1 then Version2UserID end) Version1UserID
, max (case when rn = 2 then Version2UserID end) Version2UserID
from (
 select 
  v.id, v.FolderName, u.Version1UserID, u.Version2UserID
  , ROW_NUMBER () over 
   (partition by v.id order by v.id, v.CreatedBy, 
    case 
     when v.CreatedBy > v.ModifiedBy then  u.Version1UserID 
    end desc
   ) rn 
  , v.CreatedBy, v.ModifiedBy
 from ##ver v 
 join ##veruser u 
 on u.Version1UserID in (v.CreatedBy, v.ModifiedBy)
) a 
group by id, FolderName 
order by id

Update 1:

Query does :

Join the tables.

Row numbering, over (),

Partition by Id.

Order by File id (v.id), Creator id ascending, If creator id greater than modified id, then creator id descending. (Due to second step this reordering is must)
Depends on 'rn' values, rows are transfer to columns

(You can find many examples at here)

Output :

id      FolderName  Version1UserID  Version2UserID
1       SIMPLE      321             21
2       SIMPLE1     884             500
3       File        44              321

score 0 · Answer 6 · answered Jan 23 '19 at 11:29

Try this one.

 Select a.id,folderName,b.Version2UserId as createdby,c.Version2UserId as modifiedby 
 from tblFolders as a WITH(nolock)
 inner join tblUsersMapping as b WITH(nolock) on a.createdby =b .Version1UserID 
 inner join tblUsersMapping as c WITH(nolock) on a.modifiedBy =c .Version1UserID

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

6 Answers6