Common parent of 2 dis - 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

General SQL Server Forums

New to SQL Server Programming

Common parent of 2 dis

Author

Topic

craigmacca
Posting Yak Master

142 Posts

Posted - 2013-01-07 : 05:11:16

Hi I need to find the common Id of 2 parent ids from a Tree structured table. I.e.

ParentId = 5
ParentId = 8

The common id in the example below would be 1, can this be achieved with inner joins or a select in select? If so how?

Tree Table
Id, ParentId

1, 0
2, 1
3, 0
4, 1
5, 2
6, 1
7, 0
8, 6

Many thanks

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-07 : 05:38:41

DECLARE @tab TABLE(Id int, ParentId int)
INSERT INTO @tab VALUES(1, 0),(2, 1),(3, 0),(4, 1),(5, 2),(6, 1),(7, 0),(8, 6)
SELECT * FROM @tab
SELECT t1.ParentId
FROM @tab t1
JOIN @tab t2 ON t1.Id = t2.ParentId
GROUP BY t1.ParentId
HAVING COUNT(*) = 2

--
Chandu

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-01-07 : 05:40:02

;with c1 as
(
select id, parent = parent_id from tree where id in (5,8)
union all
select id = c1.id, parent = t2.parent_id from tree t2 join c1 on c1.parent = t2.id
)
select parent
from c1
group by parent
having count(distinct id) > 1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

craigmacca
Posting Yak Master

142 Posts

Posted - 2013-01-07 : 06:54:35

Many thanks the with worked great, just one more thing, i have a stored proc that returns a int value, inside the proc I am exec another proc, this other proc is showing in my results which I do not want, is there a way to specify what is the return value?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-01-07 : 07:04:08

You mean the called SP returns a resultset?

You are returning multiple values so can't use a simple output parameter. You could concatenate to a csv string or use a table variable.

Best would be to change the called SP so it doesn't return a resultset or pipe it to a table in the call. Another option is to ignore the resultset in the client.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources