Join two tables first on PRI and then on SEC where PRI match not found

Transact-SQL
1

This logic would be very helpful. Please see the two tables and any help on a solution would be appreciated.

2

Please post DDL for the tables, insert statements for the data, what you've tried and what results you want.

3

SE [Audit_Analytics]
GO
/****** Object: Table [dbo].[tst_cc_table$] Script Date: 3/29/2017 10:58:18 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tst_cc_table$](
[cc_Employ_ID] nvarchar NULL,
[cc_Card_Num] [float] NULL,
[cc_Employ_ID2] nvarchar NULL,
[cc_First] nvarchar NULL,
[cc_Last] nvarchar NULL,
[ia_Key_Name] AS (replace(replace(replace(lower([cc_Last]+left([cc_First],(4))),' ',''),'-',''),'.',''))
) ON [PRIMARY]

GO
/****** Object: Table [dbo].[tst_hr_table$] Script Date: 3/29/2017 10:58:18 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tst_hr_table$](
[HR_Emp_ID] nvarchar NULL,
[HR_Termination_Date] [datetime] NULL,
[TERMINATION_REASON] nvarchar NULL,
[HR_First] nvarchar NULL,
[HR_Last] nvarchar NULL,
[ia_Key_Name] AS (replace(replace(replace(lower([HR_Last]+left([HR_First],(4))),' ',''),'-',''),'.',''))
) ON [PRIMARY]

GO
INSERT [dbo].[tst_cc_table$] ([cc_Employ_ID], [cc_Card_Num], [cc_Employ_ID2], [cc_First], [cc_Last]) VALUES (N'810819791', 45667, N'810819791', N'XIYAN', N'John')
INSERT [dbo].[tst_cc_table$] ([cc_Employ_ID], [cc_Card_Num], [cc_Employ_ID2], [cc_First], [cc_Last]) VALUES (N'810815275', 45669, N'810815275', N'Katherine', N'Ross')
INSERT [dbo].[tst_cc_table$] ([cc_Employ_ID], [cc_Card_Num], [cc_Employ_ID2], [cc_First], [cc_Last]) VALUES (N'810815213', 45673, N'810815213', N'Jeffrey', N'Jones')
INSERT [dbo].[tst_cc_table$] ([cc_Employ_ID], [cc_Card_Num], [cc_Employ_ID2], [cc_First], [cc_Last]) VALUES (N'101424', 45675, N'101424', N'MARTIN', N'VEGA')
INSERT [dbo].[tst_cc_table$] ([cc_Employ_ID], [cc_Card_Num], [cc_Employ_ID2], [cc_First], [cc_Last]) VALUES (N'', 45757, N'', N'VICTOR', N'COLON')
INSERT [dbo].[tst_cc_table$] ([cc_Employ_ID], [cc_Card_Num], [cc_Employ_ID2], [cc_First], [cc_Last]) VALUES (N'103271', 46639, N'103271', N'Juan', N'Rodriguez')
INSERT [dbo].[tst_hr_table$] ([HR_Emp_ID], [HR_Termination_Date], [TERMINATION_REASON], [HR_First], [HR_Last]) VALUES (N'810819791', CAST(N'2016-12-31T00:00:00.000' AS DateTime), N'INVOLUNTARY', N'XIYAN', N'john')
INSERT [dbo].[tst_hr_table$] ([HR_Emp_ID], [HR_Termination_Date], [TERMINATION_REASON], [HR_First], [HR_Last]) VALUES (N'N4r5678', CAST(N'2016-12-31T00:00:00.000' AS DateTime), N'INVOLUNTARY', N'VICTOR', N'COLON')
INSERT [dbo].[tst_hr_table$] ([HR_Emp_ID], [HR_Termination_Date], [TERMINATION_REASON], [HR_First], [HR_Last]) VALUES (N'810815275', CAST(N'2016-12-31T00:00:00.000' AS DateTime), N'INVOLUNTARY', N'Katherine', N'Ross')
INSERT [dbo].[tst_hr_table$] ([HR_Emp_ID], [HR_Termination_Date], [TERMINATION_REASON], [HR_First], [HR_Last]) VALUES (NULL, CAST(N'2016-12-31T00:00:00.000' AS DateTime), N'INVOLUNTARY', N'Juan', N'Rodriguez')
INSERT [dbo].[tst_hr_table$] ([HR_Emp_ID], [HR_Termination_Date], [TERMINATION_REASON], [HR_First], [HR_Last]) VALUES (N'810815213', CAST(N'2017-03-14T00:00:00.000' AS DateTime), N'VOLUNTARY', N'Jeffrey', N'Jones')

Mult Key Join2.jpg1149×570 195 KB

4 
SELECT tct.cc_Employ_ID
     , tct.cc_Card_Num
     , tct.cc_Employ_ID2
     , tct.cc_First
     , tct.cc_Last
     , tct.ia_Key_Name
     , tht.HR_Emp_ID
     , tht.HR_Termination_Date
     , tht.TERMINATION_REASON
     , tht.HR_First
     , tht.HR_Last
     , tht.ia_Key_Name
FROM dbo.[tst_cc_table$] tct
JOIN dbo.[tst_hr_table$] tht ON tct.cc_Employ_ID=tht.HR_Emp_ID
                             OR tct.ia_Key_Name=tht.ia_Key_Name;
1 Like
5

I think left join will give more accurate result :::: [quote="jotorre_riversidedpss.org, post:4, topic:9808"]
FROM dbo.[tst_cc_table$] tct
Left JOIN dbo.[tst_hr_table$] tht ON tct.cc_Employ_ID=tht.HR_Emp_ID
OR tct.ia_Key_Name=tht.ia_Key_Name;
[/quote]

1 Like
6

Thank you both :slight_smile: