How to exclude children from a query?

Question

Database

Tell. I'm making a note program by category, I need to implement the movement of the category. As when forming a list of categories, where you can move to exclude the category itself, its descendants and descendants of descendants. If there are no problems with the first two, then there are problems with the last ...

Answer 1

Clear.
1. Google tree building via JOIN (if the number of levels is limited)
2. SQLLite has CTE - recursion => standard tree building solution.
Build a tree from the desired node in the query by selecting their ID and then exclude them.

WHETE id NOT IN 
(
SELECT t2.id
FROM categories  AS t1
LEFT JOIN categories  AS t2 ON t2.parent_id = t1.id
WHERE t1.id = 1 ---- тут ваша категория переносимая
UNION 
SELECT t3.id
FROM categories  AS t1
LEFT JOIN categories  AS t2 ON t2.parent_id = t1.id
LEFT JOIN categories  AS t3 ON t3.parent_id = t2.id
WHERE t1.id = 1 ---- тут ваша категория переносимая
)

with recursion is more interesting:

WITH RECURSIVE CTE as (
  select 	Id, Name, parent_id, 0 as Level
  from categories
  where parent_id = @IdCategory  ---- тут ваша категория переносимая
  union all
  select 
  t2.Id, t2.Name, t2.parent_id, Level + 1
  from categories  t2
  join CTE on CTE.Id = t2.parent_id
 )
 SELECT * FROM categories
 WHERE Id NOT IN (SELECT Id FROM CTE )

I am not responsible for the syntax, there is no way to check)