I'm working with sql servre database I have two tables in the same
database.
thse are my tables,
CREATE TABLE [dbo].[Applicant] (
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[EducationDetails1] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[EducationDetails2] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[EducationDetails3] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Skills1] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Skills2] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Skills3] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Score] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Vacancy] (
[VacancyID] [int] IDENTITY (1, 1) NOT NULL ,
[EducationDetails1] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[EducationDetails2] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[EducationDetails3] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Skills1] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Skills2] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Skills3] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
I want to compare values in this two tables.if Applicant.EducationDetails==Vacancy.EducationDetails
or Applicant.Skills == Vacancy.Skills then I want to insert values to Score feild.according to that score I want to
orderby UserID and UserName.I managed to write a query,
declare @UserID int,@count int
set @UserID=2
set @count=0
if ((select DISTINCT EducationDetails1 from Applicant where UserID=@UserID)like(select EducationDetails1 from Vacancy where VacancyID=8))
begin
set @count=@count+1
end
.
.
.
.
if((select DISTINCT Skills1 from Applicant where UserID=@UserID)like(select Skills1 from Vacancy where VacancyID=8))
begin
set @count=@count+1
end
.
.
.
.
update Applicant set Score=@count WHERE UserID=@UserID
SELECT DISTINCT UserID,UserName FROM dbo.Applicant a JOIN dbo.Vacancy j
ON '%'+a.EducationDetails1+ '%' like '%'+j.EducationDetails1 + '%' OR '%'+a.EducationDetails2+ '%' like '%'+j.EducationDetails2 + '%'
OR '%'+a.Skills1+ '%' like '%'+j.Skills1 + '%' ......
where j.VacancyID=8 order by Score desc
is there any other efficient way to do this? pl help me.
:!:
