CODEWITHSUNDEEP
sqlsql-server
Robin Hansen
Robin Hansen

Reputation: 163

sql-server detect parents in parent child hierarchy

I'm implementing a recyclebin function in my project. If a whole folder is deleted I would like to see only the folder name and not all of its children (content). Here is how it might look:

+----+----------+---------------+ | ID | ParentID | Name | +----+----------+---------------+ | 3 | 1 | Css | +----+----------+---------------+ | 4 | 8 | New File.txt | +----+----------+---------------+ | 6 | 10 | Scripts.js | +----+----------+---------------+ | 7 | 3 | Styles.css | +----+----------+---------------+ | 8 | 3 | Bootstrap.css | +----+----------+---------------+

The only output I would like is row 3 and 6.

As you see there is no logical pattern in the Parent-Child relationships, hence it's difficult for me to figure out how to not display child rows.

Another alternative could be to label the rows, then I can sort my output later. For example, like this:

+----+----------+---------------+--------+ | ID | ParentID | Name | Label | +----+----------+---------------+--------+ | 3 | 1 | Css | parent | +----+----------+---------------+--------+ | 4 | 8 | New File.txt | child | +----+----------+---------------+--------+ | 6 | 10 | Scripts.js | parent | +----+----------+---------------+--------+ | 7 | 3 | Styles.css | child | +----+----------+---------------+--------+ | 8 | 3 | Bootstrap.css | child | +----+----------+---------------+--------+

Upvotes: 1

Views: 241

Answers (1)

Breian Wells
Breian Wells

Reputation: 111

What you want to do is select anything in the recycle bin that doesn't have a parent.

Select child.ID,child.ParentID,child.Name from [table] child 
left join [table] parent
on child.parentID = parent.ID
where parent.ID is null

This will get anything whose parent is not in the table

Upvotes: 5

Related Questions