Left Join to Concatenate and Left Join

harishs

Board Regular
Joined
Jul 3, 2016
Messages
50
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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top