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

sql left join multiple tables
sql join multiple tables with conditions
left join multiple matches
multiple left joins
left outer join on multiple tables
left outer join 3 tables example
left join with multiple conditions sql
which table is left in left join

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?

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

SQL Server LEFT JOIN By Practical Examples, How do you retrieve data from multiple tables in SQL without join? LEFT JOIN The SQL LEFT JOIN (specified with the keywords LEFT JOIN and ON) joins two tables and fetches all matching rows of two tables for which the SQL-expression is true, plus rows from the frist table that do not match any row in the second table.

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

An Illustrated Guide to Multiple Join, The LEFT JOIN clause allows you to query data from multiple tables. does not have any matching row from the T2 table, the query combines column values of  Table 1: id amount 1 100 2 200 3 300 4 400 Table 2: id amount 1 100 1 100 2 200 3 300 4 null Table 3: id amount 1 null 2 200 2 200 3 300 3 200 4 null id is common for each tables , how can i get output like this: id t1 t2 t3 1 100 200 null 2 200 200 400 3 300 300 500 4 400 null null i am stuck with this .

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

SQL Left Join, or left of JOIN keyword , no matter if they have matches in the right table of the JOIN keyword. Example of SQL Left Join using multiple columns for the unmatched rows of company table, the column value will be NULL. In case there is no matching row found e.g., with the country_id CN, the row in the countries table is included in the result set and the row in the locations table is filled with NULL values. Because non-matching rows in the right table are filled with the NULL values, you can apply the LEFT JOIN clause to miss-match rows between tables.

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

SQL multiple joins for beginners with examples, Thus, we gain the ability to combine multiple tables of data in order to the left join and then filter the null values because we need to eliminate  The difference is outer join keeps nullable values and inner join filters it out. So I’ll show you examples of joining 3 tables in MySQL for both types of join. How To Inner Join Multiple Tables. I want to select all students and their courses. So we need to write MySQL query to take the data from multiple tables.

Note :

  1. I did not know about how to find the query performance. I wrote only for your expected output.

  2. I am using SQL Server 2012.

  3. I did not use more than one Join.

  4. 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 :

  1. 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)

  2. 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

How to LEFT JOIN Multiple Tables in SQL, However, first make sure that your second table doesn't have records with NULL values in all columns except for the common field used for  The above query demonstrates the INNER JOIN clause which specifies the two tables that we are using and then uses the ON keyword to define the relationship or 'joining points' between the two tables. We can see that columns are identified by using TableName.ColumnName syntax so that the query knows which table to find the column we are referencing.

SQL LEFT JOIN: A Comprehensive Guide to LEFT JOIN in SQL, Suppose we have two tables A and B. The table A has four rows 1, 2, 3 and 4. rows in the right table are filled with the NULL values, you can apply the LEFT JOIN know how to apply the LEFT JOIN clause to query data from multiple tables. SQL Left join with multiple values. Ask Question Asked 5 years, 8 months ago. For doing this, I need to match two tables, to get the id of a category.

SQL INNER JOIN - Joining Two or More Tables, However, you often want to query data from multiple tables to have a SQL provides several types of joins such as inner join, outer joins ( left outer join or left​  Anyway it seems there is no need of any join to get the result for this :) Merge 3 tables (left join) Update one table rows with multiple values of second table.

Db2 LEFT JOIN Clause by Practical Examples, However, the columns from the right table will have NULL values. In other words, the LEFT JOIN clause returns all rows from the left table ( T1 ) and matching 

Comments
  • This is a bad idea because it will slow when table have many records.
  • Yes you are right, in performance sense it will little bit slow.
  • 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.
  • 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.
  • 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.