Post reply

Need Help in CTE

  • July 9, 2008 at 8:59 am

    #184482

    Dear all,

    i have a problem with the CTE and i need your help for this i have two table manager and employee_detail

    i have to retrive all the records till the leaf node if i pass any employee id

    Ex:if i pass id 1 it has to retrive all the records

    if i pass id 2-->my o/p should be 5,6,7,13,14,15,16,17,18(id)

    following is the script to create table

    create table manager (

    M_id int identity(1,1),

    F_name Varchar(30),

    L_name varchar(10),

    Designation varchar(30),

    DOJ datetime,

    Salary decimal(7,2)

    Constraint [Pk_M_Id] primary key (M_id)

    )

    create table Employee_detail (

    E_id int identity(1,1),

    Name varchar(20),

    Dept_name varchar(20),

    M_id int

    constraint [Pk_E_Id] primary key (E_id)

    constraint [Fk_M_Id] foreign key (M_id)

    references [Manager] (M_id)

    )

    --drop table manager

    --drop table employee_detail

    --select * from manager

    --

    --select getdate()

    insert into manager (f_name,l_name,designation,doj,salary)

    select 'Kapil dev','S','Manager','1985-07-09',50000 union all

    select 'Amarnath','A','Asst Manager','2001-07-09',30000 union all

    select 'Srikanth','S','Asst.Manager','2001-02-10',30000 union all

    select 'Ravi','S','Asst.Manager','2001-07-20',30000 union all

    select 'Azhar','B','Division manager','2002-06-08',21000 union all

    select 'Jadeja','A','Division manager','2002-05-09',21000 union all

    select 'Sidhu','N','Division manager','2003-04-10',21000 union all

    select 'Sachin','R','Division manager','2003-07-11',21000 union all

    select 'Mongia','N','Division manager','2003-06-13',21000 union all

    select 'Kumble','N','Division manager','2004-02-17',21000 union all

    select 'Prasath','V','Division manager','2003-01-20',21000 union all

    select 'Manjurekar','S','Division manager','2004-04-21',21000 union all

    select 'Robin','S','Area manager','2005-05-09',18000 union all

    select 'Ganguly','S','Area manager','2006-04-09',18000 union all

    select 'Dravid','R','Area manager','2007-01-09',18000 union all

    select 'Shewag','V','Area manager','2005-02-09',18000 union all

    select 'Harbhajan','R','Area manager','2005-03-09',18000 union all

    select 'Ojha','R','Area manager','2006-08-09',18000 union all

    select 'Badrinath','R','Area manager','2007-04-09',18000 union all

    select 'Balaji','S','Area manager','2008-02-09',18000 union all

    select 'Sree santh','S','Area manager','2006-05-09',18000 union all

    select 'Dinesh','K','Area manager','2005-01-09',18000 union all

    select 'Chawla','P','Area manager','2005-02-09',18000 union all

    select 'Goni','S','Area manager','2005-03-09',18000 union all

    select 'Sharma','R','Area manager','2005-04-09',18000 union all

    select 'Pathan','I','Area manager','2006-06-09',18000 union all

    select 'Dhoni','R','Area manager','2007-05-09',18000 union all

    select 'Gambir','G','Area manager','2005-05-09',18000 union all

    select 'Yuvaraj','S','Area manager','2006-07-09',18000

    Insert into Employee_detail

    (Name,Dept_name,M_id)

    select 'kapil dev','MG' union all

    select 'Amarnath','AMG',1 union all

    select 'Srikanth','AMG',1 union all

    select 'Ravi','AMG',1 union all

    select 'Azhar','DM',2 union all

    select 'Jadeja','DM',2 union all

    select 'Sidhu','DM',2 union all

    select 'Sachin','DM',3 union all

    select 'Mongia','DM',3 union all

    select 'Kumble','DM',4 union all

    select 'Prasath','DM',4 union all

    select 'Manjurekar','DM',4 union all

    select 'Robin','AM',5 union all

    select 'Ganguly','AM',5 union all

    select 'Dravid','AM',5 union all

    select 'Shewag','AM',6 union all

    select 'Harbhajan','AM',6 union all

    select 'Ojha','AM',6 union all

    select 'Badrinath','AM',8 union all

    select 'Balaji','AM',8 union all

    select 'Sree Santh','AM',9 union all

    select 'Dinesh','AM',9 union all

    select 'Chawla','AM',10 union all

    select 'Goni','AM',10 union all

    select 'Sharma','AM',11 union all

    select 'Pathan','AM',11 union all

    select 'Dhoni','AM',12 union all

    select 'Gambir','AM',12 union all

    select 'Yuvaraj','AM',12

  • July 9, 2008 at 9:01 am

    #840230

    I don't see an attempt at a CTE here. please show some work.

  • July 9, 2008 at 9:05 am

    #840242

    WITH Managers AS

    (

    SELECT top 1 E_id, Name, M_id, '' f_name

    FROM Employee_detail

    WHERE M_id IS NULL

    UNION ALL

    SELECT e.E_id,e.Name, e.M_id,m.f_name

    FROM Employee_detail e INNER JOIN Manager m

    ON e.m_id = m.M_id

    )

    SELECT * FROM Managers where m_id=3

  • July 9, 2008 at 9:39 am

    #840291

    I fixed the second insert statement (needs a null or ID for the third column in the first select), ran the code, and got results that seem to make sense from the data given.

    What problem are you running into that you need help on?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • July 9, 2008 at 10:02 am

    #840323

    Gosh... this is an "adjacency" hierarchy on steroids... my feeling is that the tables are not correctly designed... there should be a table where ALL the managers and employees are in the table and they should each have an EmployeeID, a ManagerID, and a JobID where the different types of jobs, including the various manager positions, should be listed in a separate table.

    Once that's done, convert this puppy into a nested set model... at least for lookups.

    --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)

  • July 9, 2008 at 12:36 pm

    #840449

    Yeah, it definitely could do with a rebuild, but even the original question isn't clear. He says he needs help with a CTE, but the CTE is just fine.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • July 9, 2008 at 11:59 pm

    #840633

    Hi GSquared,

    Hope you are not clear with my Question the criteria is if i give the E_id 2 it produce only the result of 5 6 7

    this is correct upto you but as far as my criteria if i give the E_id 2

    it should produce the result like

    2-->5 6 7 13 14 15 16 17 18.

    Because E_id 2's child are 5 6 7

    and E_id 5's child are 13 14 15

    & E_id's 6's child are 16 17 18

    & E_id 7 doesn't have any child.

    It means it has to traverse till the leaf node ...

    Hope you are clear now.

  • July 10, 2008 at 2:05 am

    #840676

    Hi guys i have got the solution for my problem..

    The Following is the coding

    alter procedure Hierarchy

    (

    @m_id int

    )

    as

    begin

    declare @cnt int

    declare @e_id int

    create table #temp (id int)

    if @m_id=1

    begin

    insert into #temp select e_id from employee_detail where m_id=@m_id

    select @cnt=min(id) from #temp

    while @cnt < (select max(id) from #temp)

    begin

    insert into #temp select e_id from employee_detail where m_id=@cnt

    set @cnt= @cnt+1

    end

    /*select E_id,name from Employee_detail where E_id in(Select id from

    #temp)*/

    select e.E_id,e.name AS [Employee name],m.F_name [Manager name] from Employee_detail e,manager m where e.E_id in(Select id from

    #temp) and e.m_id=m.m_id

    end

    --

    else

    begin

    -- drop table #temp

    -- create table #temp (id int)

    insert into #temp select e_id from employee_detail where m_id=@m_id

    select @cnt=min(id) from #temp

    select @e_id=max(id) from #temp

    while @cnt <= @e_id

    begin

    insert into #temp select e_id from employee_detail where m_id=@cnt

    set @cnt= @cnt+1

    end

    select e.E_id,e.name AS [Employee name],m.F_name [Manager name] from Employee_detail e,manager m where e.E_id in(Select id from

    #temp) and e.m_id=m.m_id

    end

    drop table #temp

    end

    sample execution---

    Hierarchy 4

    ---

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

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