0

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.

ichthyocentaurs
  • 2,173
  • 21
  • 35

2 Answers2

1
SELECT * FROM aTable where resource_path not like concat('%', code, '%');
Agnelo Dsouza
  • 126
  • 10
0

@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;
Dorad
  • 3,413
  • 2
  • 44
  • 71