Here is a quick solution using CHARINDEX
😎
USE tempdb;
GO
CREATE TABLE [dbo].[x_JobMatching](
[CVID] [int] NULL,
[JobNoticeID] [int] NULL,
[isMatch] [bit] NULL,
CONSTRAINT [x_JobMatching_UQ1] UNIQUE NONCLUSTERED
(
[CVID] ASC,
[JobNoticeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[x_JobMatching] ([CVID], [JobNoticeID], [isMatch]) VALUES (1925, 45, 0)
INSERT [dbo].[x_JobMatching] ([CVID], [JobNoticeID], [isMatch]) VALUES (6590, 69, 0)
INSERT [dbo].[x_JobMatching] ([CVID], [JobNoticeID], [isMatch]) VALUES (459, 13, 0)
INSERT [dbo].[x_JobMatching] ([CVID], [JobNoticeID], [isMatch]) VALUES (2456, 19, 0)
INSERT [dbo].[x_JobMatching] ([CVID], [JobNoticeID], [isMatch]) VALUES (4569, 11, 0)
INSERT [dbo].[x_JobMatching] ([CVID], [JobNoticeID], [isMatch]) VALUES (4000, 904, 0)
INSERT [dbo].[x_JobMatching] ([CVID], [JobNoticeID], [isMatch]) VALUES (24, 56, 0)
INSERT [dbo].[x_JobMatching] ([CVID], [JobNoticeID], [isMatch]) VALUES (900, 24, 0)
INSERT [dbo].[x_JobMatching] ([CVID], [JobNoticeID], [isMatch]) VALUES (24, 19, 0)
ALTER TABLE [dbo].[x_JobMatching] ADD CONSTRAINT [DF_x_JobMatching_isMatch] DEFAULT ('false') FOR [isMatch]
GO
declare @t_JobNoticeID table (cvid int, JobNoticeID int)
insert into @t_JobNoticeID values(2456, 24);
insert into @t_JobNoticeID values(4000, 124);
insert into @t_JobNoticeID values(245, 9);
insert into @t_JobNoticeID values(2456, 19);
insert into @t_JobNoticeID values(4000, 904);
insert into @t_JobNoticeID values(4000, 11);
insert into @t_JobNoticeID values(24, 19);
;WITH T_JOB AS
(
SELECT [CVID]
, STUFF((SELECT ', ' + Convert(varchar(10),A.[JobNoticeID])
FROM @t_JobNoticeID A
Where A.[CVID]=B.[CVID] FOR XML PATH('')),1,1,'') As [All_JobNoticeID]
From @t_JobNoticeID B
Group By [CVID]
)
SELECT
XJ.CVID
,XJ.JobNoticeID
,SIGN(ISNULL(CHARINDEX(CONVERT(VARCHAR(10),XJ.JobNoticeID,1),TJ.All_JobNoticeID,1),0)) AS isMatch
FROM dbo.x_JobMatching XJ
LEFT OUTER JOIN T_JOB TJ
ON XJ.CVID = TJ.cvid;
DROP TABLE dbo.x_JobMatching;
Results
CVID JobNoticeID isMatch
----------- ----------- --------
24 19 1
24 56 0
459 13 0
900 24 0
1925 45 0
2456 19 1
4000 904 1
4569 11 0
6590 69 0