Parent & Child Problem - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Parent & Child Problem

Author

Topic

LearnEveryDay
Starting Member

37 Posts

Posted - 2005-09-15 : 09:02:19

Hi Guys

This is my table

ID name ParentID
------------------------------
1 TestParent1 <null>
2 TestChid1 1
3 TestChild2 1
4. TestParent2 <null>
5 TestParent3 <null>
6 TestParent 4 <null>
7 TestChild3 6
8 TestChild4 6

basically I want to get records from the above table those who does not have child. In the above case

I want records like

4. TestParent2 <null>
5 TestParent3 <null>

How would i write the sql query to get it worked??

Cheers,

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-09-15 : 09:18:29

Is this what you're looking for?


select	[Columns I want]
from	[my table]
where	ParentID is NULL

Be One with the Optimizer
TG

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-09-15 : 09:18:44

Select A.*
From <yourTable> A
Left Join <yourTable> B
On A.id = B.parentId
Where B.id is null

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

LearnEveryDay
Starting Member

37 Posts

Posted - 2005-09-15 : 09:20:58

No..no..no

Well, the table has got some parents with children and some does not.
I want parent records which does not have any children.

Can i do it as a simple query or do i need to write cursor??

cheers,

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-09-15 : 09:21:01

Dang! I forgot to read the question before answering again...sigh...

Be One with the Optimizer
TG

X002548
Not Just a Number

15586 Posts

Posted - 2005-09-15 : 09:28:53

You can't. Nothing distinguishes the difference between a child and a Parent.

Do you have some real DDL and data we can work with?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-15 : 09:31:41

How do you expect these from your sample data?

4. TestParent2 <null>
5 TestParent3 <null>

Madhivanan

Failing to plan is Planning to fail

LearnEveryDay
Starting Member

37 Posts

Posted - 2005-09-15 : 09:38:27

Hi,

I am just working on a simple web project with parent and child technique.

and I am simply trying to make a parent which does not have any child, as a child to another parent. I thought it might be beyond my knowledge, but it makes sense doesn't it?. if a parent does not have a child why can't i use that parent to another parent's child.

May be my thought is wrong. but please explain me......

LearnEveryDay
Starting Member

37 Posts

Posted - 2005-09-15 : 09:41:45

quote:
Originally posted by madhivanan

How do you expect these from your sample data?

4. TestParent2 <null>
5 TestParent3 <null>

Madhivanan

Failing to plan is Planning to fail

I can see those two records does not have any child ;)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-09-15 : 09:51:48

read my post -^

I gave you the query to find parent's with no children...

as far as:

quote:
if a parent does not have a child why can't i use that parent to another parent's child.

how do you distinguish if:
The parent is a parent without a child, that the child of the parent is not really the child but is the child of a different parent