SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2005
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Compare two tables

    I'm working with sql servre database these are tables in my databas.

    Code:
    CREATE TABLE [dbo].[JobSeekerEducation] (
    	[JobSeekerEducationID] [bigint] IDENTITY (1, 1) NOT NULL ,
    	[JobSeekerID] [bigint] NULL ,
    	[EducationTypeID] [tinyint] NULL ,
    	[EducationCourseID] [smallint] NULL ,
    	
    	
    ) ON [PRIMARY]
    GO
    
    CREATE TABLE [dbo].[JobSeekerSkill] (
    	[JobSeekerSkillID] [bigint] IDENTITY (1, 1) NOT NULL ,
    	[JobSeekerID] [bigint] NULL ,
    	[SkillCategoryID] [smallint] NULL ,
    	[SkillID] [smallint] NULL ,
    	
    ) ON [PRIMARY]
    GO
    
    CREATE TABLE [dbo].[JobSeeker] (
    	[JobSeekerID] [bigint] IDENTITY (1, 1) NOT NULL ,
    	[FirstName] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
    	[Surname] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
    	[FullName] [varchar] (200) COLLATE Latin1_General_CI_AS NULL ,
    	[KnownAs] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
    	[DateOfBirth] [smalldatetime] NULL ,
    	[HomeAddressLine1] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
    	[HomeAddressLine2] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
    	[HomeCity] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
    	[HomeRegion] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
    	[HomePostalcode] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
    	[HomeCountryID] [smallint] NULL ,
    	[EmailAddress] [varchar] (200) COLLATE Latin1_General_CI_AS NULL ,
    	[HomeTelephone] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
    	[WorkTelephone] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
    	[Mobilephone] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
    	[HomeFax] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
    	[ContactMethod1ID] [tinyint] NULL ,
    	[ContactMethod2ID] [tinyint] NULL ,
    	[PreferredEmploymentTypeID1] [tinyint] NULL ,
    	[PreferredEmploymentTypeID2] [tinyint] NULL ,
    	[PreferredJobSectorID1] [smallint] NULL ,
    	[PreferredJobSectorID2] [smallint] NULL ,
    	[PreferredCountryLocationID1] [smallint] NULL ,
    	[PreferredCountryLocationID2] [smallint] NULL ,
    	[PreferredOrganisationTypeID1] [tinyint] NULL ,
    	[PreferredOrganisationTypeID2] [tinyint] NULL ,
    	[PreferredJobRoleID1] [smallint] NULL ,
    	[PreferredJobRoleID2] [smallint] NULL 
    	
    ) ON [PRIMARY]
    GO
    
    CREATE TABLE [dbo].[t_JobVacancy] (
    	[JobVacancyID] [bigint] IDENTITY (1, 1) NOT NULL ,
    	[ReferenceCode] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
    	[JobSectorID] [smallint] NULL ,
    	[JobRoleID] [smallint] NULL ,
    	[OtherJobRole] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
    	[NumberOfVacancies] [tinyint] NULL ,
    	[JobVacancyStatusID] [tinyint] NULL ,
    	[OrganisationID] [bigint] NULL ,
    	[EmploymentTypeID] [tinyint] NULL ,
    	[CountryLocationID] [smallint] NULL ,
    	[VacancyPostedDate] [smalldatetime] NULL ,
    	[VacancyExpiryDate] [smalldatetime] NULL ,
    	[JobStartDate] [smalldatetime] NULL ,
    	[RecruitmentAgentID] [bigint] NULL ,
    	[SalaryRangeID] [tinyint] NULL ,
    	[SalaryRate] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
    	[Benefits] [varchar] (1000) COLLATE Latin1_General_CI_AS NULL ,
    	[RequiredExperienceLevel] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
    	[JobDescription] [varchar] (2000) COLLATE Latin1_General_CI_AS NULL ,
    	[EducationTypeID1] [tinyint] NULL ,
    	[EducationCourseID1] [smallint] NULL ,
    	[EducationTypeID2] [tinyint] NULL ,
    	[EducationCourseID2] [smallint] NULL ,
    	[EducationTypeID3] [tinyint] NULL ,
    	[EducationCourseID3] [smallint] NULL ,
    	[EducationTypeID4] [tinyint] NULL ,
    	[EducationCourseID4] [smallint] NULL ,
    	[EducationTypeID5] [tinyint] NULL ,
    	[EducationCourseID5] [smallint] NULL ,
    	[SkillCategoryID1] [smallint] NULL ,
    	[SkillID1] [smallint] NULL ,
    	[SkillCategoryID2] [smallint] NULL ,
    	[SkillID2] [smallint] NULL ,
    	[SkillCategoryID3] [smallint] NULL ,
    	[SkillID3] [smallint] NULL ,
    	[SkillCategoryID4] [smallint] NULL ,
    	[SkillID4] [smallint] NULL ,
    	[SkillCategoryID5] [smallint] NULL ,
    	[SkillID5] [smallint] NULL ,
    	[Comments] [varchar] (1000) COLLATE Latin1_General_CI_AS NULL 
    ) ON [PRIMARY]
    GO
    view:

    Code:
    CREATE VIEW viewJobApplication
    AS
    SELECT DISTINCT JobSeeker.FullName,JobSeeker.DateOfBirth,JobSeeker.HomeAddressLine1,JobSeeker.HomeAddressLine2,JobSeeker.HomeCity,JobSeeker.HomeRegion,JobSeeker.HomePostalcode,JobSeeker.HomeTelephone,JobSeeker.WorkTelephone,JobSeeker.Mobilephone,
    JobSeeker.HomeFax, JobSeeker.EmailAddress,JobSeeker.ContactMethod1ID, JobSeeker.ContactMethod2ID,JobSeeker.JobSeekerID,JobSeeker.PreferredEmploymentTypeID1,JobSeeker.PreferredEmploymentTypeID2,JobSeeker.PreferredJobSectorID1,JobSeeker.PreferredJobSectorID2,
                    JobSeeker.PreferredCountryLocationID1,JobSeeker.PreferredCountryLocationID2,JobSeeker.PreferredOrganisationTypeID1,
    		JobSeeker.PreferredOrganisationTypeID2,JobSeeker.PreferredJobRoleID1,JobSeeker.PreferredJobRoleID2,JobSeekerEducation.EducationTypeID,JobSeekerEducation.EducationCourseID,
    		JobSeekerEducation.OtherEducationCourse,JobSeekerSkill.SkillCategoryID,JobSeekerSkill.SkillID    
    			
    FROM         JobSeeker INNER JOIN
                          JobSeekerEducation ON JobSeekerEducation.JobSeekerID = JobSeeker.JobSeekerID 
    			INNER JOIN
                          JobSeekerSkill ON JobSeekerSkill.JobSeekerID = JobSeeker.JobSeekerID 
    
    
    GO

    I want to compare values in t_JobVacancy table and viewJobApplication view if values are eual I want to insert values to Score feild.according to that score I want to
    orderby JobSeekerID and FullName.This is my sql query,

    Code:
    select DISTINCT JobSeekerID,FullName,DateOfBirth,HomeAddressLine1,HomeAddressLine2,HomeCity,HomeRegion,HomePostalcode,HomeTelephone,WorkTelephone,Mobilephone,
    HomeFax, EmailAddress,ContactMethod1ID, ContactMethod2ID, 
    
    	case when a.PreferredEmploymentTypeID1 = j.EmploymentTypeID 
                then 1 else 0 end
    	+case when a.PreferredEmploymentTypeID2 = j.EmploymentTypeID 
                then 1 else 0 end
    	+case when a.PreferredJobSectorID1= j.JobSectorID 
                then 1 else 0 end
    	+case when a.PreferredJobSectorID2= j.JobSectorID 
    		then 1 else 0 end
          	+case when a.PreferredCountryLocationID1= j.CountryLocationID 
                then 1 else 0 end
         	+case when a.PreferredCountryLocationID2= j.CountryLocationID 
                then 1 else 0 end 
    	+case when a.EducationTypeID= j.EducationTypeID1 
                then 1 else 0 end 
    	+case when a.EducationTypeID= j.EducationTypeID2 
                then 1 else 0 end 
    	+case when a.EducationTypeID= j.EducationTypeID3 
                then 1 else 0 end 
    	+case when a.EducationTypeID= j.EducationTypeID4 
                then 1 else 0 end 
    	+case when a.EducationTypeID= j.EducationTypeID5 
                then 1 else 0 end 
    	+case when a.EducationCourseID= j.EducationCourseID1 
                then 1 else 0 end 
    	+case when a.EducationCourseID= j.EducationCourseID2 
                then 1 else 0 end 
    	+case when a.EducationCourseID= j.EducationCourseID3 
                then 1 else 0 end 
    	+case when a.EducationCourseID= j.EducationCourseID4 
                then 1 else 0 end 
    	+case when a.EducationCourseID= j.EducationCourseID5 
                then 1 else 0 end 
    	+case when a.SkillCategoryID= j.SkillCategoryID1 
                then 1 else 0 end 
    	+case when a.SkillCategoryID= j.SkillCategoryID2
                then 1 else 0 end 
    	+case when a.SkillCategoryID= j.SkillCategoryID3 
                then 1 else 0 end 
    	+case when a.SkillCategoryID= j.SkillCategoryID4 
                then 1 else 0 end 
    	+case when a.SkillCategoryID= j.SkillCategoryID5 
                then 1 else 0 end
    
    	as score            
      FROM viewJobApplication a INNER JOIN t_JobVacancy j 
    ON a.PreferredJobRoleID1=j.JobRoleID OR a.PreferredJobRoleID2=j.JobRoleID
    where j.JobVacancyID=1 order by score desc

    my problem is I get more than one score (diffrent scores) for same jobseekrID how can I solove this.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    look at your view and ask yourself this: if a single jobseeker has 3 courses and 9 skills, how many rows does the view produce for this jobseeker? that's right, 27

    so when you join the view to the vacancy table, this job seeker will appear multiple times, right?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2005
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes, Thats true, how can I solove this problem.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    not sure what you should do

    but joining jobseeker to courses at the same time as jobseeker to skills is going to give you this cross join effect so i would have to say don't do that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Oct 2005
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    do you have any Idea how can I solove this problem? I tried many ways but still I could not solove problem.with out that join effect how can I do this?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you can solve this problem by not joining to both skills and courses at the same time
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    Oct 2005
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    can you give me the example then I can understand clearly.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    sure
    Code:
    CREATE VIEW JobseekerCourses
    AS
    SELECT JobSeeker.FullName
         , JobSeeker.DateOfBirth
         , JobSeeker.HomeAddressLine1
         , JobSeeker.HomeAddressLine2
         , JobSeeker.HomeCity
         , JobSeeker.HomeRegion
         , JobSeeker.HomePostalcode
         , JobSeeker.HomeTelephone
         , JobSeeker.WorkTelephone
         , JobSeeker.Mobilephone
         , JobSeeker.HomeFax
         , JobSeeker.EmailAddress
         , JobSeeker.ContactMethod1ID
         , JobSeeker.ContactMethod2ID
         , JobSeeker.JobSeekerID
         , JobSeeker.PreferredEmploymentTypeID1
         , JobSeeker.PreferredEmploymentTypeID2
         , JobSeeker.PreferredJobSectorID1
         , JobSeeker.PreferredJobSectorID2
         , JobSeeker.PreferredCountryLocationID1
         , JobSeeker.PreferredCountryLocationID2
         , JobSeeker.PreferredOrganisationTypeID1
         , JobSeeker.PreferredOrganisationTypeID2
         , JobSeeker.PreferredJobRoleID1
         , JobSeeker.PreferredJobRoleID2
         , JobSeekerEducation.EducationTypeID
         , JobSeekerEducation.EducationCourseID
         , JobSeekerEducation.OtherEducationCourse
      FROM JobSeeker 
    INNER 
      JOIN JobSeekerEducation 
        ON JobSeekerEducation.JobSeekerID 
         = JobSeeker.JobSeekerID
    Code:
    CREATE VIEW JobseekerSkillz0rz
    AS
    SELECT JobSeeker.FullName
         , JobSeeker.DateOfBirth
         , JobSeeker.HomeAddressLine1
         , JobSeeker.HomeAddressLine2
         , JobSeeker.HomeCity
         , JobSeeker.HomeRegion
         , JobSeeker.HomePostalcode
         , JobSeeker.HomeTelephone
         , JobSeeker.WorkTelephone
         , JobSeeker.Mobilephone
         , JobSeeker.HomeFax
         , JobSeeker.EmailAddress
         , JobSeeker.ContactMethod1ID
         , JobSeeker.ContactMethod2ID
         , JobSeeker.JobSeekerID
         , JobSeeker.PreferredEmploymentTypeID1
         , JobSeeker.PreferredEmploymentTypeID2
         , JobSeeker.PreferredJobSectorID1
         , JobSeeker.PreferredJobSectorID2
         , JobSeeker.PreferredCountryLocationID1
         , JobSeeker.PreferredCountryLocationID2
         , JobSeeker.PreferredOrganisationTypeID1
         , JobSeeker.PreferredOrganisationTypeID2
         , JobSeeker.PreferredJobRoleID1
         , JobSeeker.PreferredJobRoleID2
         , JobSeekerSkill.SkillCategoryID
         , JobSeekerSkill.SkillID    
      FROM JobSeeker 
    INNER 
      JOIN JobSeekerSkill 
        ON JobSeekerSkill.JobSeekerID 
         = JobSeeker.JobSeekerID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast
    Join Date
    Oct 2005
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I used two views but still I have same problem. I think I found the problem its not in the view it happen when we matching two coloums. for example
    Code:
    case when JobSeekerEducation.EducationTypeID=t_JobVacancy.EducationTypeID1 
                then 1 else 0 end

    becouse Education table EducationTypeID has 5 rows and JobVacancy.EducationTypeID1 has one row. when JobVacancy.EducationTypeID1 result match Education.EducationTypeID result sometimes I get two values both 1 and 0 that is why my resut are duplicated. is there any way to solove this problem?

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    i would recommend that you try to understand that you can't join a one-to-many relationship to another table and expect to get single results
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •