Compare two tables

•

What is DaniWeb IT Discussion Community?

You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 397,862 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,513 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our MS SQL advertiser:

Thread Tools Display Modes

Views: 13994 | Replies: 1

Previous Post in Thread

Next Post in Thread

•

Join Date: Aug 2005

Posts: 26

Reputation: aish is an unknown quantity at this point

Rep Power: 4

Solved Threads: 0

aish

Offline

Light Poster

Compare two tables

Nov 21st, 2005

I'm working with sql servre database I have two tables in the same
database.
thse are my tables,

Help with Code Tags

(Toggle Plain Text)

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,

Help with Code Tags

(Toggle Plain Text)

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