0

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.
:!:

2
Contributors
1
Reply
2
Views
12 Years
Discussion Span
Last Post by noman78
0

HERE IS THE SAMPLE FOR CREATE AN UPDATE QUERY USING COMPARISON IN TWO TABLES
<sAMPLE qUERY<
UPDATE Table1
SET t.field1 = t.field3 + s.field2
FROM table1 t, table2 s
WHERE t.field4 = s.field1
AND s.ord_date =
(SELECT MAX(field5) FROM table3)

<end query>
this is the best and most efficient way

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.