SQL NOT LIKE Wildcard Condition on Inner Join

Question

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?

Answer 1

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).

Answer 2

Change it to

ON NOT (v.hr_grade LIKE g.HR_Grade + '%' )

EDIT:

Removed the ON inside the brackets.