August 22, 2014 at 10:29 pm
This is my code, table and result,
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);
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]
My RESULT A1 was
CVIDAll_JobNoticeID
--------------------------------------
24 19
245 9
245624, 19
4000124, 904, 11
I've another table named x_JobMatching and data s follow,
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)
/****** Object: Default [DF_x_JobMatching_isMatch] Script Date: 08/23/2014 12:26:10 ******/
ALTER TABLE [dbo].[x_JobMatching] ADD CONSTRAINT [DF_x_JobMatching_isMatch] DEFAULT ('false') FOR [isMatch]
GO
My question is -
How update statement looks like compare RESULT A1 with x_JobMatching?
So, my FINAL RESULT shown as follow,
CVIDJobNoticeIDisMatch
1925450
6590690
459130
2456191
4569110
40009041
24560
900240
24191
Please help
August 22, 2014 at 11:09 pm
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
August 24, 2014 at 6:44 pm
Maybe I'm missing something here but I think there's no reason to construct a delimited list at all.
SELECT a.CVID, a.JobNoticeID, IsMatch=CASE WHEN b.JobNoticeID IS NOT NULL THEN 1 ELSE 0 END
FROM dbo.x_JobMatching a
LEFT JOIN @t_JobNoticeID b ON a.CVID = b.cvid AND a.JobNoticeID = b.JobNoticeID;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 24, 2014 at 11:23 pm
dwain.c (8/24/2014)
Maybe I'm missing something here but I think there's no reason to construct a delimited list at all.
SELECT a.CVID, a.JobNoticeID, IsMatch=CASE WHEN b.JobNoticeID IS NOT NULL THEN 1 ELSE 0 END
FROM dbo.x_JobMatching a
LEFT JOIN @t_JobNoticeID b ON a.CVID = b.cvid AND a.JobNoticeID = b.JobNoticeID;
It is my understanding that the delimited list is what has to be matched, otherwise as you say Dwain, it is a straight forward join.
😎
August 25, 2014 at 1:11 am
Eirikur Eiriksson (8/24/2014)
dwain.c (8/24/2014)
Maybe I'm missing something here but I think there's no reason to construct a delimited list at all.
SELECT a.CVID, a.JobNoticeID, IsMatch=CASE WHEN b.JobNoticeID IS NOT NULL THEN 1 ELSE 0 END
FROM dbo.x_JobMatching a
LEFT JOIN @t_JobNoticeID b ON a.CVID = b.cvid AND a.JobNoticeID = b.JobNoticeID;
It is my understanding that the delimited list is what has to be matched, otherwise as you say Dwain, it is a straight forward join.
😎
The reason I posted that (and I'm not saying this is the case here), is that I've seen quite a few occasions where the OP wants to lead us down a path that is unnecessary due to their own preconceptions.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 25, 2014 at 1:19 am
dwain.c (8/25/2014)
Eirikur Eiriksson (8/24/2014)
dwain.c (8/24/2014)
Maybe I'm missing something here but I think there's no reason to construct a delimited list at all.
SELECT a.CVID, a.JobNoticeID, IsMatch=CASE WHEN b.JobNoticeID IS NOT NULL THEN 1 ELSE 0 END
FROM dbo.x_JobMatching a
LEFT JOIN @t_JobNoticeID b ON a.CVID = b.cvid AND a.JobNoticeID = b.JobNoticeID;
It is my understanding that the delimited list is what has to be matched, otherwise as you say Dwain, it is a straight forward join.
😎
The reason I posted that (and I'm not saying this is the case here), is that I've seen quite a few occasions where the OP wants to lead us down a path that is unnecessary due to their own preconceptions.
Hence the importance of getting the "what" out of the OP, preferably ignoring the "how";-)
In this case I have this hunch that you are spot on though.
😎
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply