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
 Common parent of 2 dis
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

craigmacca
Posting Yak Master

141 Posts

Posted - 01/07/2013 :  05:11:16  Show Profile  Reply with Quote
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
2218 Posts

Posted - 01/07/2013 :  05:38:41  Show Profile  Reply with Quote
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
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/07/2013 :  05:40:02  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
;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.
Go to Top of Page

craigmacca
Posting Yak Master

141 Posts

Posted - 01/07/2013 :  06:54:35  Show Profile  Reply with Quote
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?
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/07/2013 :  07:04:08  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
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.05 seconds. Powered By: Snitz Forums 2000