August 16, 2004 at 12:59 am
hi,
I have the following query, can anyone help me the same in T-SQL. It will create the tree structure report. ie., Boss and the related subordinates kind of report.
SELECT
cat_desc,
cat_id
,
parent_id
,
level
FROM t1 m
WHERE not exists (SELECT 1
FROM t1 d
WHERE d.cat_id != m.cat_id
CONNECT BY PRIOR d.cat_id = d.parent_id
START WITH d.cat_id = m.cat_id)
AND cat_type like '%'
CONNECT BY PRIOR cat_id = parent_id
START WITH parent_id is null
rgds,
venkat
August 16, 2004 at 1:02 am
Hi
What does 'connect by prior' do ? What is the results you are expecting ?
August 16, 2004 at 6:50 am
There is an example in BOL. see: "expanding hierarchies".
There are other 'better' ways of doing this, submit some test data and an example of what you want your result to look like.
/rockmoose
You must unlearn what You have learnt
August 16, 2004 at 6:37 pm
connect by prior=one of the nicest bits of sql syntax in Oracle!
My approach to this in SQL Server is to define a function that returns a table of id's. This needs to be recursive to be flexible.
This would mean that you can do "select ... from t1 where id in (select id from dbo.fnChildrenOf(t1.id))" type of thing.
This is fine for finding all children of a particular node, but it sounds like you want the full structure in one hit, which could be a but more complicated.
At the end of the day, you can work pretty much any sort of magic with creative SQL, cursors etc, so I'm sure it's possible.
I don't have a function to hand as I am at home, but I'll try to dig one I've written before out at work and give you an example.
August 18, 2004 at 1:36 am
Recursive N-Tier heirarchial queries aren't really possible in SQL 2000 without using a temporary table to store the results as you make them (in my experience).
2005 has a built in view function that solves this problem. Define the parent / child relationship and it creates the heirarchial dataset for you.
Till then, a temp table or table variable will be your friend.
Julian Kuiters
juliankuiters.id.au
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply