self join? not exisits?? - 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

SQL Server Development (2000)

self join? not exisits??

Author

Topic

steve_o
Starting Member

3 Posts

Posted - 2005-04-14 : 06:45:52

Hallo,

I have one table with a field called parent items, and another field called child items. I would like to find all the records in the table where the parent item does not appear in any records as a child item.

any clues would be much appreciated...

Regards,
Stephen.

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-04-14 : 06:52:57

Try somthing like


SELECT PT.ParentID
FROM ParentTable PT LEFT JOIN ChildTable CT
	ON PT.ParentID = CT.ParentID
WHERE CT.ParentID IS NULL

Edit:
Oops just re-read the question, try this instead

SELECT PT.ParentID
FROM YourTable PT LEFT JOIN YourTable CT
ON PT.ParentID = CT.ChildID
WHERE CT.ChildID IS NULL

Andy

Beauty is in the eyes of the beerholder

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2005-04-14 : 10:51:30

This should also work

Select *
From tblItem a where not exists
(    Select 1 
     From tblItem b
     Where a.parent = b.child)

.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.
A candle loses nothing by lighting another candle

steve_o
Starting Member

3 Posts

Posted - 2005-04-15 : 01:17:09

Thanks for the help - works great.
Regards,
Stephen.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources