Link to home
Start Free TrialLog in
Avatar of garethtnash
garethtnashFlag for United Kingdom of Great Britain and Northern Ireland

asked on

MS SQL Inner Join - Multiple Join Parameters

Hello All,

Appreciate a little guidance if possible..

I have two tables, both have an Employee ID, IFS Business Unit and IFS Cost Centre..

I want to update the one table based on all three of the above not matching - so I have --

CREATE TABLE #Worker_Status(
	[Job Seeker ID] [nvarchar](50) NOT NULL,
	[Employee ID] [NvarChar] (7) NULL,	
	[IFS Cost Centre] [Nvarchar] (5) NULL,
	[IFS Business Unit] [Nvarchar] (5) NULL
)

CREATE TABLE #Dev_Hourly_TA(
	[Centre] [NvarChar] (7) NOT NULL,
	[Staff No] [int] NOT NULL,
	[IFS Company] [Nvarchar] (10) NOT NULL
)

BEGIN
Update #Dev_Hourly_TA set [Error] = 1, [Error Reason] = 'Employee ID not listed in FG'
Where [Staff No] not in (

select [Staff No] from #Dev_Hourly_TA T Inner Join #Worker_Status on T.[Staff No] = W.[Employee ID], T.[Centre]] = W.[IFS Cost Centre], T.[IFS Company] = W.[IFS Business Unit]  )

END

Open in new window


The bit I'm struggling with is the join - as I want the join to bring  back only records where T.[Staff No] = W.[Employee ID], T.[Centre]] = W.[IFS Cost Centre], T.[IFS Company] = W.[IFS Business Unit]

Is this possible?

Thank you
ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of garethtnash

ASKER

Perfect  Thank you - Comments taken onboard :)