mysql where a column does not contain regular expression with value from another column-CodePudding

I have the below table and I need to find all rows where the resource_path does not contain the code in the string.

id | code | resource_path
1   abc01    /this/is/path/to/resource/abc01/xyz.jpg
2   abc02    /this/is/path/to/resource/abc02/xyz.png
3   abc02    /this/is/path/to/resource/xyz.png

I have tried the following query

SELECT * FROM aTable WHERE resource NOT IN (select code from aTable) ORDER BY id DESC LIMIT 10;

but this returns everything. Thanks in advance.

CodePudding user response：

SELECT * FROM aTable where resource_path not like concat('%', code, '%');

CodePudding user response：

@Agnelo Dsouza answer is great. I put it in a fiddle and added another option, as it might give you a better performence:

SELECT * FROM aTable where INSTR(resource_path,code) = 0;