SQLTeam.com | Weblogs | Forums

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

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

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

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')

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

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

Thank you both :slight_smile: