MS SQL

Compare two tables

Please support our MS SQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Aug 2005

Posts: 26

Reputation: aish is an unknown quantity at this point

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 
 MS SQL Syntax (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
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 
 MS SQL Syntax (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
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.
:!:

•

Join Date: Nov 2005

Posts: 40

Reputation: noman78 is an unknown quantity at this point

Solved Threads: 0

noman78

Offline

Light Poster

Re: Compare two tables

Nov 22nd, 2005

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