correlated subquery

  • Hi,

    I have a table in the following structure

    MgrID EmpID EmployeeName

    0 1 Ezhil

    1 2 Alex

    1 3 Jack

    2 4 Allen

    3 5 Rose

    I want to find the child details of the particular node if specific EmpID is given.

    I already got the output through recursive function and also through CTE.

    But, i want to know whether the same can be achieved through corelated subqueries. If, yes how to achieve that?

    Waiting for the reply eagerly.Thanks in advance.

  • select MgrID, EmpID, EmployeeName

    from emp e

    where MgrID in (select EmpID from emp b

    where b.EmpID = 1)

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Hi Mike,

    By using your query i can able to get only the immediate childs of the parent.

    For example when i gave 1 as input to that query, i have retrieving only the direct child of 1, in our case iam receiving 2 and 3.But i cannot able to receive all child of parent.

    Expecting output:

    Input - 1

    Output - 2 ,4, 3, 5

  • if that's the output you're expecting, then you will need to use a CTE or recursive funtion, which you have already done.

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Is it possible to achieve the same using 'JOINS'?

  • ezhil-649633 (12/4/2009)


    Is it possible to achieve the same using 'JOINS'?

    Hi,

    try this, on inner join

    create table #temp1

    (

    MID int,

    EID int,

    ENAME varchar(10)

    )

    insert into #temp1

    select 1,1,'A'

    union all

    select 1,2,'AA'

    union all

    select 1,3,'AAA'

    union all

    select 1,4,'AAAA'

    union all

    select 2,1,'B'

    union all

    select 2,2,'BB'

    union all

    select 3,1,'C'

    union all

    select 3,2,'CC'

    union all

    select 3,3,'CCC'

    select * from #temp1

    declare @child_param int

    set @child_param = 3

    select a.* from #temp1 a

    inner join

    (select distinct MID from #temp1

    where EID = @child_param)as b

    on a.Mid= B.MID

  • When i run your query against my database i have got this as output.

    Input

    EID = 3

    Output:

    MID EID E.Name

    11A

    12AA

    13AAA

    14AAAA

    31C

    32CC

    33CCC

    But, i cannot able to under stand how this solution will fit for my suituation.Can you explain bit more to me.

    Thanks

  • ezhil-649633 (12/3/2009)


    But, i want to know whether the same can be achieved through corelated subqueries. If, yes how to achieve that?

    For a finite number of levels, probably. For an unknown number of levels, no.

    The recursive CTE you used has the problem of needing to recalculate the tree for whatever you're looking up every time it's used. That's probably ok if the underlying data is changing all the time. But, if the data is relatively static, then using "Nest Set Hierarchies" is probably the better way to go. The queries for downlines, uplines, and hierarchical aggregations are lightning fast because of the way they're done.

    Here's a link for the Nest Set things... a GOOGLE of the subject will return more information on the subject that you can imagine... some of it is also pretty useless... 😛

    http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html

    If memory serves, the code isn't written for SQL Server and can stand some optimizations there. Also (if I remember correctly), the code that converts from the Adjacency Model to the Nested Set model has a small bug in it... it always leaves off the last node. I don't remember exactly what I did to fix it but I do remember the fix being fairly easy to find and repair.

    As a side bar, when I do have to do such a thing as hierarchies, I tend to embed the Nested Set model into the Adjacency Model so that I have the best of both worlds.

    There's a third way to do such things using a column that contains the entire "upline" path for each node. It's also quite effective especially for stable hierarchies. Here's some working example code for that method...

    --=======================================================================================

    -- Setup some test data... note that nothing in this section is part of the actual

    -- solution.

    --=======================================================================================

    --===== Setup a "quiet" environment

    SET NOCOUNT ON

    --===== Create a table to hold some test data.

    -- This is NOT part of the solution

    CREATE TABLE #yourtable

    (

    ID INT,

    ParentID INT,

    Descrip VARCHAR(20)

    )

    --===== Populate the test table with 2 "trees" of data

    INSERT INTO #yourtable

    (ID,ParentID,Descrip)

    SELECT 9,NULL,'County 1' UNION ALL --Note NULL, this is top node of "Tree 1"

    SELECT 2,9 ,'C1 Region 1' UNION ALL

    SELECT 4,9 ,'C1 Region 2' UNION ALL

    SELECT 3,2 ,'C1 R1 Unit 1' UNION ALL

    SELECT 5,2 ,'C1 R1 Unit 2' UNION ALL

    SELECT 6,4 ,'C1 R2 Unit 1' UNION ALL

    SELECT 7,NULL,'County 2' UNION ALL --Note NULL, this is top node of "Tree 2"

    SELECT 8,7 ,'C2 Region 1' UNION ALL

    SELECT 1,9 ,'C1 Region 3'

    --=======================================================================================

    -- The following code makes a Hierarchy "sister" table with strings that are used

    -- to traverse various hierarchies.

    --=======================================================================================

    --===== Create and seed the "Hierarchy" table on the fly

    SELECT ID,

    ParentID,

    Descrip,

    Level = 0, --Top Level

    HierarchyString = CAST(STR(ID,5) AS VARCHAR(8000))+' '

    INTO #Hierarchy

    FROM #yourtable

    WHERE ParentID IS NULL

    --===== Declare a local variable to keep track of the current level

    DECLARE @Level INT

    SET @Level = 0

    --===== Create the hierarchy in the HierarchyString

    WHILE @@ROWCOUNT > 0

    BEGIN

    SET @Level = @Level + 1

    INSERT INTO #Hierarchy

    (ID, ParentID, Descrip, Level, HierarchyString)

    SELECT y.ID,y.ParentID,y.Descrip, @Level, h.HierarchyString + STR(y.ID,5) + ' '

    FROM #yourtable y

    INNER JOIN #Hierarchy h

    ON y.ParentID = h.ID --Looks for parents only

    AND h.Level = @Level - 1 --Looks for parents only

    END

    --=======================================================================================

    -- Now, demo the use of the sister table

    --=======================================================================================

    --===== Display the entire tree with indented descriptions according to the Level

    SELECT ID,

    ParentID,

    Level,

    LEFT(REPLICATE(' ',Level*2)+descrip,30),

    HierarchyString

    FROM #Hierarchy

    ORDER BY HierarchyString

    --===== Select only the "downline" for ID 2 including ID 2

    SELECT ID,

    ParentID,

    Level,

    LEFT(REPLICATE(' ',Level*2)+descrip,30),

    HierarchyString

    FROM #Hierarchy

    WHERE HierarchyString LIKE '% 2 %'

    ORDER BY HierarchyString

    drop table #Hierarchy

    drop table #yourtable

    Obviously, you wouldn't drop the tables once created. For relatively static tables, you'd rerun the code when rows were added, deleted, or modified.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply