SQL NOT LIKE Wildcard Condition on Inner Join

sql, t-sql

Solution

As almost always in SQL, there are some ways to do it:

-- 1. using outer join where the outer table don't match
SELECT DISTINCT Employee_ID
FROM @tmp_vo_hr_acp_staff v
LEFT JOIN hr_grades g ON (v.hr_grade LIKE g.HR_Grade + '%') -- wildcard search
WHERE g.id IS NULL -- use any non nullable column from hr_grades here

-- 2. using EXISTS for set difference
SELECT DISTINCT Employee_ID
FROM @tmp_vo_hr_acp_staff v
WHERE NOT EXISTS (
    SELECT 'x' FROM hr_grades g WHERE v.hr_grade LIKE g.HR_Grade + '%'
)

-- 3. using the less popular ANY operator for set difference
SELECT DISTINCT Employee_ID
FROM @tmp_vo_hr_acp_staff v
WHERE NOT v.hr_grade LIKE ANY (
    SELECT g.HR_Grade + '%' FROM hr_grades g
)

Personally, I don't like using joins for filtering, so I would probably use option 2. If `hr_grades` is a much smaller than `@tmp_vo_hr_acp_staff`, though, you can benefit from using the option 3 (as the set can be determined beforehand and than cached with a single read operation).

Problem

I have a table called hr_grades that contains employee pay grades such as:- ``` ID hr_grade 1 PR07 2 AC04 ``` I run two stored procedures. One that returns employees whose grades are in this table, and one that does not. These stored procedures carry out a number of different tasks to prepare the data for loading into the end system. I want the query to carry out a wildcard search on the rows in the grades table. So for example for employees whose grades are in the table ``` SELECT DISTINCT Employee_ID, FROM @tmp_vo_hr_acp_staff v INNER JOIN hr_grades g ON v.hr_grade LIKE g.HR_Grade + '%' -- wildcard search ``` The reason for the wildcard is that the hr_grades can be like AC04(1) , AC04(2) etc. This works fine. However I am struggling to get the reverse of this working. ``` SELECT DISTINCT Employee_ID, FROM @tmp_vo_hr_acp_staff v INNER JOIN hr_grades g ON v.hr_grade NOT LIKE g.HR_Grade + '%' ``` Any ideas how I could get this to wildcard search to work on a NOT LIKE condition?

Original source