atoms atoms - 29 days ago 6
SQL Question

Job Alert, checking saved searche per Job to return respective UserIDs

I'm trying to work out how to calculate if a users search matches that of a newly posted job.

I plan to start with a JobID and work back to find all UserIDs that have a saved search.

Jobs

JobID WorkingHoursID JobTypeID AlertChecked
4 1 1 0
5 3 4 0





UserJobSearches

UserID JobSearchID Saved Alert SearchName
1 2043 1 1 User1
2 2044 1 1 User2





Job Searches

JobSearchID SearchDate Radius
2043 2016-10-27 12:42:19 NULL
2044 2016-10-27 12:43:19 NULL





JobSearchWorkingHours

JobSearchID WorkingHoursID
2043 1
2044 2





JobSearchJobTypes

JobSearchID JobTypeID
2043 1
2044 1





-- get UserIDs from UserJobSearches using JobsearchID from sub query
Select UserID FROM UserJobSearches WHERE JobSearchID IN(

-- get jobsearch id from jobsearches
SELECT JobSearchID FROM JobSearches WHERE JobSearchID IN(

-- get the WorkingHoursID
SELECT JobSearchID FROM JobSearchWorkingHours WHERE (
WorkingHoursID IS NULL
OR
WorkingHoursID IN (
SELECT WorkingHoursID FROM Jobs WHERE JobID = 4
)
)

) AND JobSearchID IN(

-- get the JobTypeID
SELECT JobSearchID FROM JobSearchJobTypes WHERE (
JobTypeID IS NULL
OR
JobTypeID IN(
SELECT JobTypeID FROM Jobs WHERE JobID = 4
)
)

)

)


My issue is around excluding a search if it has a condition that doesnt match entirely.
I also need to say if the search doesnt contain a field dont exclude the result

So I almost need to say get the WorkingHoursID and the JobSearchID WHERE it matches that of a search or isnt included?

I've made multiple attempts to write something for this. But keep finding myself stuck and confused. Any advice or help would be greatly appreicated!

Answer

Somethig similar would work. You need to join all your search criteria with a INNER JOIN and then optionally join the jobs on matching LEFT OUTER JOIN. Finally join the Jobs table again to get all the results. There are better ways to do this as the amount of search criteria increases, however, this should be fine for two search conditions.

SELECT
    UserID,
    JobSearchID,
    JobID,
    MatchesHours=CASE WHEN X.MatchesHoursJobID IS NULL  THEN 0 ELSE 1 END,
    MatchesJobType=CASE WHEN X.MatchesJobTypeJobID IS NULL  THEN 0 ELSE 1 END
FROM
(
    SELECT
        UserID,
        JobSearchID,
        MatchesHoursJobID=CASE WHEN HoursMatch.JobID IS NULL THEN 0 ELSE 1 END,
        MatchesJobTypeJobID=CASE WHEN JobTypeMatch.JobID IS NULL THEN 0 ELSE 1 END
    FROM 
        UserJobSearches UJS
        INNER JOIN JobSearches JS ON JS.JobSearchID=UJS.JobSearchID
        INNER JOIN JobSearchJobTypes JSJT ON JSJT.JobSearchID =UJS.JobSearchID 
        INNER JOIN JobSearchWorkingHours JSWH ON JSWH.JobSearchID =UJS.JobSearchID 
        LEFT OUTER JOIN Jobs HoursMatch ON Jobs.WorkingHoursID=JSWH.WorkingHoursID
        LEFT OUTER JOIN Jobs JobTypeMatch ON Jobs.JobTypeID =JSWH.JobTypeID   
)AS X
INNER JOIN Jobs ON  Jobs.JobID=MatchesHoursJobID OR Jobs.JobID=MatchesJobTypeJobID
Comments