Sql: Find Missing Folders Paths In Splitting Hierarchies

I have a table which contains folders paths. This table contains four columns: DirID, BaseDirID, DirLevel and DisplayPath. DirID - The folder's ID. BaseDirID - The ID of the first

Solution 1:

Using this added path (11,2,'U\V\Z\L\O\Q\R\S\T') to show multiple missing folders in a path:

with cte as (
select BaseDirID, DisplayPath =left(DisplayPath,len(DisplayPath)-charindex('\',reverse(DisplayPath)))
from t
where DirLevel >1andnotexists (
  select1from t i
  where t.BaseDirId = i.BaseDirId
    and i.DisplayPath =left(t.DisplayPath,len(t.DisplayPath)-charindex('\',reverse(t.DisplayPath)))
    )
unionallselect BaseDirID, DisplayPath =left(DisplayPath,len(DisplayPath)-charindex('\',reverse(DisplayPath)))
from cte t
wherenotexists (
  select1from t i
  where t.BaseDirId = i.BaseDirId
    and i.DisplayPath =left(t.DisplayPath,len(t.DisplayPath)-charindex('\',reverse(t.DisplayPath)))                                                   
    )
)
selectdistinct*from cte

rextester demo: http://rextester.com/CEVGZ96613

returns:

+-----------+-----------------+
| BaseDirID |   DisplayPath   |
+-----------+-----------------+
|         1 | A\B             |
|         1 | A\B\C\D         |
|         1 | A\B\F\G         |
|         2 | U\V             |
|         2 | U\V\M\L         |
|         2 | U\V\W\X         |
|         2 | U\V\Z           |
|         2 | U\V\Z\L         |
|         2 | U\V\Z\L\O       |
|         2 | U\V\Z\L\O\Q     |
|         2 | U\V\Z\L\O\Q\R   |
|         2 | U\V\Z\L\O\Q\R\S |
+-----------+-----------------+