Left Join to Concatenate and Left Join

[harishs]

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.

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

 

[xenou]

maybe I am missing something obvious but I think you could just add:

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]

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.