Search code examples
sqlsqliteleft-joinsql-like

Select records that do not start with "xxx" joined onto another table

I am trying get a list of items (to eventually be deleted). My inner select returns the following items from the collections table (which is correct).

Inner Select

I want to use the above results to to join on the files table and select items that do not start with the above path so I can then delete them.

I tried using this query to select them however, if I filter the results, the records that I wanted to exclude exist in the result set.

SELECT files.path, c.path FROM files LEFT JOIN (
  SELECT json_extract(info, '$.path') AS path FROM collections 
  WHERE 
  json_extract(info, '$.path') like 'C:/Users/untun/Documents%'
  AND json_extract(info, '$.path') != 'C:/Users/untun/Documents'
) AS c ON c.path LIKE files.path || '%'

-- Adding the next lines returns the two records below
-- GROUP BY files.path
-- HAVING files.path like 'C:/Users/untun/Documents/vscode/projects/csharp%'

Result

Solution

  • You must reverse the operands of the operator LIKE and filter out the rows that don't match in the WHERE clause:

    SELECT f.path 
FROM files AS f 
LEFT JOIN (
  SELECT json_extract(info, '$.path') AS path 
  FROM collections 
  WHERE json_extract(info, '$.path') LIKE 'C:/Users/untun/Documents_%'
) AS c ON f.path LIKE c.path || '%'
WHERE c.path IS NULL;

    Also, the condition:

    json_extract(info, '$.path') like 'C:/Users/untun/Documents%'
AND 
json_extract(info, '$.path') != 'C:/Users/untun/Documents'

    can be simplified to:

    json_extract(info, '$.path') LIKE 'C:/Users/untun/Documents_%'