CODEWITHSUNDEEP
sqlsql-serverparent-childparentexists
PowerUnderwhelming
PowerUnderwhelming

Reputation: 3

SQL Query: Add a "HasChild" column in results

I have what I think is an easy question, but the answer is eluding me. I have a single table with a "parent" column that relates back to other records in the table. What I'm looking to do is a select statement that has a "HasChild" bit column in my results. So if for example my table looks like this:

ID | ParentID 
1  | null
2  | 1
3  | 2
4  | null
5  | 4
6  | 1

Then I'm looking for a select that returns these results:

ID | ParentID | HasChild
1  | null     | true
2  | 1        | true
3  | 2        | false
4  | null     | true
5  | 4        | false
6  | 1        | false

As always, help is greatly appreciated. Thanks in advance.

Upvotes: 0

Views: 136

Answers (1)

GolezTrol
GolezTrol

Reputation: 116110

select
  x.ID,
  x.ParentId,
  case exists (select 'x' from YourTable y where y.ParentId = x.Id) then
    true
  else
    false
  end as HasParent
from
  YourTable x

Maybe you can leave out the case, but I'm not sure:

select
  x.ID,
  x.ParentId,
  exists (select 'x' from YourTable y where y.ParentId = x.Id) as HasParent
from
  YourTable x

Upvotes: 1

Related Questions