SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Orphaned Records from Hierarchy
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sent_sara
Constraint Violating Yak Guru

India
366 Posts

Posted - 08/15/2013 :  20:52:57  Show Profile  Reply with Quote
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

USA
37 Posts

Posted - 08/15/2013 :  22:08:16  Show Profile  Reply with Quote
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
Go to Top of Page

sent_sara
Constraint Violating Yak Guru

India
366 Posts

Posted - 08/16/2013 :  13:21:08  Show Profile  Reply with Quote
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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000