Orphaned Records from Hierarchy

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Orphaned Records from Hierarchy

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2013-08-15 : 20:52:57

Can anybody help me out how to find the orphaned value from the below parent/child hierarchy Table.


create table dbo.Hier(parent varchar(100), child varchar(100))

insert into Hier
select 'subramanium','Manickam' union all
select 'subramanium','Munuswamy' union all
select 'Munuswamy','senthil' union all
select 'Munuswamy','sasi' union all
select 'Munuswamy','uma' union all
select 'manickam','vijay' union all
select 'manickam','bhavani' union all
select 'manickam','dhanam' union all
select 'uma','varsha'

Delete from Hier where child='uma'

I tried:


select parent from Hier 
where parent  not in(select Child from Hier)
  and parent <> 'subramanium'

Getting resultset as:


parent
======
uma

I need to know whether my select statement is correct or not,if its correct,how to write the same in CTE?

jethrow
Starting Member

37 Posts

Posted - 2013-08-15 : 22:08:16

Do you just mean like this?

With Hier (parent, child) As (
	select 'subramanium','Manickam' union all
	select 'subramanium','Munuswamy' union all
	select 'Munuswamy','senthil' union all
	select 'Munuswamy','sasi' union all
	select 'Munuswamy','uma' union all
	select 'manickam','vijay' union all
	select 'manickam','bhavani' union all
	select 'manickam','dhanam' union all
	select 'uma','varsha' )

Select	parent
From	Hier 
Where	parent Not In (
		Select	Child
		From	Hier
		Where	child <> 'uma' )
And	parent <> 'subramanium';

Microsoft SQL Server Noobie

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2013-08-16 : 13:21:08

Perfect.. Thanks.

quote:

Originally posted by jethrow

Do you just mean like this?

With Hier (parent, child) As (
	select 'subramanium','Manickam' union all
	select 'subramanium','Munuswamy' union all
	select 'Munuswamy','senthil' union all
	select 'Munuswamy','sasi' union all
	select 'Munuswamy','uma' union all
	select 'manickam','vijay' union all
	select 'manickam','bhavani' union all
	select 'manickam','dhanam' union all
	select 'uma','varsha' )

Select	parent
From	Hier 
Where	parent Not In (
		Select	Child
		From	Hier
		Where	child <> 'uma' )
And	parent <> 'subramanium';

Microsoft SQL Server Noobie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources