Left Join to Concatenate and Left Join

harishs

New Member
Joined
Jul 3, 2016
Messages
48
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi Experts,

I am trying to Left Join a field from Table 2 to Table 1 (just like excel Vlookup), have successfully lookup for 2 columns however need your assistance to lookup with 3rd fields which I need to concatenate first and do lookup.

Code:
SELECT b.*, a.[Skill Type] AS Skill_Set, c.[Revision] AS Post_Revision
FROM ([tbl01_Apr-19] AS b 
LEFT JOIN grade_skillset AS a ON a.[Grade] = b.[Grade_1]) 
LEFT JOIN Emp_Sal_Details AS c ON c.[Emp ID] = b.[EMP_ID_1];

Now i need to Concatenate 2 fields from 'tbl01_Apr-19' called State & Skill_Type and do a look up with this concatenated field in table called 'Emp_Sal_Details' field State_Skillset

Regards,
Harish S
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,764
Office Version
  1. 2019
Platform
  1. Windows
maybe I am missing something obvious but I think you could just add:

Code:
LEFT JOIN Emp_Sal_Details AS d ON (b.[State] & b.[Skill_Type]) = d.[State_Skillset]

Notice that we already had join on that table aliased as c so with this join we aliased it as d
 

rondeondo

Board Regular
Joined
Aug 15, 2012
Messages
156
Harish
if you have a table to join based on two field values, you can put two conditions in the ON clause:

.. Join Table1
ON table1.field1 = table2.field1 AND table1.field2 = table2.field2

this will have the same effect of concatenating the fields in both tables before doing the join.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,809
Messages
5,574,435
Members
412,592
Latest member
moonsugar
Top