| Author |
 Topic  |
|
|
craigmacca
Posting Yak Master
132 Posts |
 Posted - 01/07/2013 : 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
Flowing Fount of Yak Knowledge
India
1704 Posts |
Posted - 01/07/2013 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 01/07/2013 : 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
132 Posts |
Posted - 01/07/2013 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 01/07/2013 : 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. |
|
|
| |
Topic |
|
|
|
Common parent of 2 dis
