Ndtyjky

I have 3 tables in my database called Jobs, JobApplications, Candidates.

here they are.

Jobs

    JobId  | JobTitle

    ---------------------------

    2115   | Software Engineer

    2154   | Accountant

    4562   | Sales Manager

    4569   | Civil Engineer

JobApplications

    JobApplicationId  | CandidateId | JobId

    ---------------------------

    8845              | 1120        | 2154

    8912              | 1120        | 4569        

    9120              | 1555        | 2115

    9450              | 1899        | 2115

    9458              | 1991        | 4569

    9488              | 1889        | 4569

Candidates

    CandidateId | Email

    ----------------------------

    1120        | can1@mail.com

    1555        | can2@mail.com

    1889        | can3@mail.com

    1991        | can4@mail.com

What I want: A table with candidates and jobIds based on their previous applications.

Ex: if someone has applied for the "software engineer" job position, I need the all other jobs with title "software engineer" except the applied job to a table along with candidateId.



Is there any way to achieve this using SQL?

Can anybody help me?

The expected output would be like below

    CandidateId | Suggest_jobId

    ------------------------------

    1120        | 3565

    1120        | 8956

    1120        | 4565

    1889        | 8965

    1889        | 4568

So single candidate may have multiple job suggestions.

We can make use of a simple CTE to do the job

 WITH cte AS 

 (

     SELECT j.JobId,

            j.JobTitle,

            ja.CandidateId 

     FROM JobApplications ja

     JOIN Jobs j ON j.JobId=ja.JobId  

 )

 SELECT j.JobTitle,

        c.CandidateId 

 FROM Jobs j

 JOIN cte c ON j.JobTitle like CONCAT('%',c.JobTitle,'%') AND c.JobId!=j.JobId

I have simple and basic solution for you. I have used table function to split applied jobs first. Then I used these results to find similarity using Sanal Sunny's script.

The table function creation script:

CREATE  FUNCTION [dbo].[Tbl_Fn_Split](

    @InputText VARCHAR(8000) 

  , @Delimiter VARCHAR(8000) = ' ' -- delimiter that separates items

) RETURNS @List TABLE (Result VARCHAR(8000))



BEGIN

DECLARE @aResult VARCHAR(8000)

WHILE CHARINDEX(@Delimiter,@InputText,0) <> 0

BEGIN

    SELECT

        @aResult=RTRIM(LTRIM(SUBSTRING(@InputText,1,CHARINDEX(@Delimiter,@InputText,0)-1))),

        @InputText=RTRIM(LTRIM(SUBSTRING(@InputText,CHARINDEX(@Delimiter,@InputText,0)+LEN(@Delimiter),LEN(@InputText))))



    IF LEN(@aResult) > 0

        INSERT INTO @List SELECT @aResult

    END



    IF LEN(@InputText) > 0

        INSERT INTO @List SELECT @InputText 



    RETURN



END

The finding similarity script which is based on Sanal Sunny's answer:

 WITH cte AS 

 (

     SELECT j.JobId,

            j.JobTitle,

            ja.CandidateId,

            A.Result

     FROM JobApplications ja

     JOIN Jobs j ON j.JobId=ja.JobId  

     CROSS APPLY (SELECT * FROM DBO.[Tbl_Fn_Split](j.JobTitle,' ')) A

 )

 SELECT DISTINCT c.CandidateId

        ,j.JobId

        ,j.JobTitle

 FROM Jobs j

 JOIN cte c ON j.JobTitle LIKE '%'+c.Result+'%'AND c.JobId!=j.JobId