Hierarchy query in Sql sever

Question

0.00/5 (No votes)

See more:

I m working on table called FUT_USR_DETAILS
this table have columns child_id,Parent_id,Org_id
these columns have parent child relationship. org id is super parent.
suppose org id 1 is grand parent, parentid 1 is parent(for first time org id will be parent_id also as it creates parent id) and child id is child1
if child id1 creates user it becomes parent and it's entry goes in parentid column but for this entry it's org id column will have same value as in case when child id1 was child.
for all children, grandchildren,grand_grandchildren,so on, the org id will have same value.

i want to write a query to get level of count of hierarchy of particular user.
that is starting from grandparent(org id) how many generation levels are present for that org id .
i wrote a query to find this count. but i need to explicitly specify each rg id in query. this query does not work for entire table at time. query is

SQL

SELECT count(*)
FROM EXTDBA.fut_user_details
WHERE UD_ORG_ID ='F00761'  //here i m specifying org id explicitly 
AND UD_child_ID  IN
  (SELECT UD_PARENTID FROM EXTDBA.fut_user_details WHERE UD_ORG_ID ='F00761'
  )
AND UD_child_ID!=UD_ORG_ID;

programmatically if i want to do this i'll do it like this.
select distinct org id from table. take it in array
anf for each item in array ie for each org id i'll run for llop
like

SQL

for(i=0;i<arrorgid.count;i++)>
{
SELECT count(*)
FROM EXTDBA.fut_user_details
WHERE UD_ORG_ID =arrOrgId[i]  //here i m specifying org id explicitly 
AND UD_child_ID  IN
  (SELECT UD_PARENTID FROM EXTDBA.fut_user_details WHERE UD_ORG_ID =arrOrgId[i]
  )
AND UD_child_ID!=UD_ORG_ID;

}

but i m asked to do it by quering database directly.
can someone tell me how to do so

Posted 30-Nov-11 2:36am

Member 8081020

Updated 30-Nov-11 2:40am

#realJSOP

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jörgen Andersson · Answer 1 · 2011-11-30T10:47:00

I don't get the point with the orgid being the parentid for the first level. You're mixing up users with organisations.
Let the orgid define the organisational unit or department and so on, and use a normal id/parentid schema where the toplevel has <null> as the parentid.

Anyway, try this query:

SQL

SELECT  UD_child_ID,UD_PARENTID,LEVEL
FROM    EXTDBA.fut_user_details
CONNECT BY PRIOR UD_child_ID = UD_PARENTID
START WITH UD_PARENTID = 'F00761'

#realJSOP · Answer 2 · 2011-11-30T02:44:00

There are a few ways you can do this:

0) Pass the ID's to a stored procedure in a comma-delimited string, and in that stored proc, parse the string of IDs into a temporary table, and then perform a query using the temp table together with your actual data table.

1) Perform a series of queries, one for each ID, and merge the retruned datasets together in the code that called the stored proc

2) Create a stored proc that accepts a limted number of ID parameters (integers I suspect), and performs a query using those IDs, and call this stored proc as many times as necessary, merging the returned datasets in your code.

Personally, I'd go with the first option.

Hierarchy query in Sql sever

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0