Is It Possible??

Question

0.00/5 (No votes)

See more:

I have a table groups
contains

GroupID   GroupName ChildId
1           G1        0(0-Means Root Group)
2           G2        0
3           G3        0
4           G4        0
5           G5        1(The GroupId of First Row)
6           G6        5(The GroupId of Fifth Row)
7           G7        2(The GroupId of 2nd Row)
8           G8        7(The GroupId of 7th Row)
9           G9        8(The GroupId of 8th Row)

Now I Want The Group Name Of Root Group
Through Sql query only I want to get
the Name Of the main Group Through its child Id.
For Ex. If I select 9(Group Id) It Is Linked With Main Id-2
9->8->7-2-0(Means Group Name-G2)-Where we will get zero it is over

Thank You
Sukhen Dass

Posted 10-Feb-10 1:58am

sukhen dass

Updated 10-Feb-10 18:16pm

v5

Add a Solution

5 solutions

Solution 2

Nope (I guess): you cannot do that with a SQL query.
I suppose you have to iterate (or possibly recurse).
BTW I'm not a database expert, but you table hardly follows any normalization rule...
:)

Posted 10-Feb-10 2:33am

CPallini

Updated 10-Feb-10 2:34am

v2

Solution 3

It is tough to do with a single SQL query. Create a stored procedure which takes groupid and recurse on the children. Each iteration will insert row into a temporary table and return the table once done.

I am removing urgent from your post :)

Posted 10-Feb-10 4:15am

N a v a n e e t h

Solution 1

Sorry my friend

your question is not understood. can you descripb it in more proper way?

Posted 10-Feb-10 2:29am

Khaniya

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 · Accepted Answer · 2010-02-14T06:45:00

If you by any chance would be using Oracle the answer would be:

SQL

SELECT  GroupID
FROM    Groups
WHERE   ChildID = 0
START   WITH GroupID = 9
CONNECT BY PRIOR ChildID = GroupID

If you have sqlserver 2008 you can use subquery factoring

SQL

With  t AS (
    SELECT  GroupID,ChildId
    FROM    Groups g
    WHERE   GroupID = 9
        UNION ALL
    SELECT  o.GroupID,o.ChildId
    FROM    Groups g join t
        ON  g.GroupID = t.ChildId
    )
SELECT  t.GroupID
FROM    t
WHERE   ChildId = 0

AspDotNetDev · Accepted Answer · 2010-02-10T18:28:00

You have 3 options.

What you are using is a recursive data structure. It will require that you use recursion or iteration to find the root node. You will need a while loop that repeatedly performs a SELECT statement on the previous node to find it's "child" (in recursive terminology, this would usually be called the "parent", as a child can have one parent but one parent may have many children).

An alternative would be to use a "common table expression" to create a recursive query, though this feature is only available in SQL Server 2008. You can read more about that technique here.

Finally, you can also create a recursive function that basically does what the while loop does, but in a recursive fashion. Note, however, that some versions of SQL Server have limitations on the allowable depth of recursion, though I'm not sure if that is restricted to just stored procedures (you can create a function instead of a stored procedure).