locked
How to write Query to get manager and Group and Emp relation ship RRS feed

  • Question

  • User194385433 posted

    Hi,

    I joined several tables and I am getting output in some Temporary Table like Below:

    Table Emp:
    
    create table #Emp(Id int, Name varchar(20), Designation varchar(10), Teamlead int,Grp varchar(5))
    
    insert into #Emp values (15,'Ganesh','DM',0,'A')
    insert into #Emp values (19,'sam','DM',0,'A')
    insert into #Emp values (2,'Ravi','TL',1,'A')
    insert into #Emp values (3,'Krishna','SE',0,'A')
    insert into #Emp values (4,'Ram','SE',0,'A')
    insert into #Emp values (5,'Hari','TL',1,'A')
    insert into #Emp values (8,'kailash','SE',0,'A')
    insert into #Emp values (9,'John','TL',1,'B')
    insert into #Emp values (10,'Vinod','SE',0,'B')
    insert into #Emp values (11,'kumar','SE',0,'B')
    insert into #Emp values (13,'Mahesh','SE',0,'C')
    insert into #Emp values (14,'Arvind','DM',0,'D')
    insert into #Emp values (25,'Kamalakar','SE',0,'D')
    
    Id Name         Designation   Teamlead  goup
    15 Ganesh         DM             0       A
    19 sam            DM             0       A
    2  Ravi           TL             1       A
    3  Krishna        SE             0       A
    4  Ram            SE             0       A
    5  Hari           TL             1       A
    8  kailash        SE             0       A
    9  John           TL             1       B
    10 Vinod          SE             0       B
    11 kumar          SE             0       B
    13 Mahesh         SE             0       C
    14 Arvind         DM             0       D
    25 Kamalakar      SE             0       D
    
    
    Group table
    
    id  groupid    employeid  gname
    1     10           15       A
    2     10           19       A
    3     10           2        A
    4     10           3        A
    5     10           4        A
    6     10           5        A
    7     10           8        A
    8     11           9        B
    9     11           10       B
    10    11           11       B
    11    12           12       C
    12    13           14       D
    13    13           14       D
    
    
    TeamLead Emp relation,Table Name : teamlead_emp
    
    Id  tl_id     emp_id
    1    2          10
    2    2          11
    3    5          4
    4    9          3
    5    9          8  
    
    
    
    How to write to a query to get TeamLead and respective SE in order.
    output:
    
    ID     Name        Designation    TeamLead  group
    15     Ganesh         DM             0        A
    19     Sam            DM             0        A
    2      Ravi           TL             1        A
    10     Vinod          SE             0        A
    11     Kumar          SE             0        A
    5      Hari           TL             1        A
    4      Ram            SE             0        A
    9      John           TL             1        B
    3      Krishna        SE             0        B 
    8      Kailash        SE             0        B 
    13     Mahesh         SE             0        C
    14     Arvind         DM             0        D
    15     Kamalakar      SE             0        D
    
    
    Thanks,

    Wednesday, March 2, 2016 9:26 PM

Answers

  • User-808054615 posted

    Try:

    select 
        e.Name,
        e.Designation,
        e.Teamlead,
        e.Grp,
        t.tl_id 
    from #Emp1 as e
    left join #TeamLead as t
        on t.emp_id = e.Id
        
    union all
        
    select 
        Name,
        Designation,
        Teamlead,
        Grp + case when GROUPING_ID(Name) = 0 then '' else 'Total' end,
        Id 
    from #Emp2
    group by grouping sets
        ((Name, Designation, Teamlead, Grp, Id), (Grp))
    
    order by
        Grp,
        tl_id,
        Teamlead desc
    

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 3, 2016 5:42 PM

All replies

  • User-808054615 posted

    Hi,

    Try:

    select 
        e.*
    from Group as g
    inner join Emp as e
        on e.Id = g.employeid
    left join TeamLead as t
        on t.emp_id = e.Id
    left join Group as gt
        on gt.employeid = t.tl_id
    order by
        coalesce(gt.id, g.id),
        g.id
    

    Hope this help

    Wednesday, March 2, 2016 9:54 PM
  • User194385433 posted

    Thanks for you reply,

    I made some changes to my logic...below is the code:

    create table #Emp1(Id int, Name varchar(20), Designation varchar(10), Teamlead int,Grp varchar(5))
    
    insert into #Emp1 values (15,'Ganesh','DM',0,'A')
    insert into #Emp1 values (19,'sam','DM',0,'A')
    insert into #Emp1 values (3,'Krishna','SE',0,'A')
    insert into #Emp1 values (4,'Ram','SE',0,'A')
    insert into #Emp1 values (8,'kailash','SE',0,'A')
    
    insert into #Emp1 values (22,'Kishora','DM',0,'B')
    insert into #Emp1 values (10,'Vinod','SE',0,'B')
    insert into #Emp1 values (11,'kumar','SE',0,'B')
    
    insert into #Emp1 values (13,'Mahesh','SE',0,'C')
    insert into #Emp1 values (23,'Venktesh','SE',0,'C')
    
    insert into #Emp1 values (14,'Arvind','DM',0,'D')
    
    insert into #Emp1 values (25,'Ramesh','SE',0,'E')
    
    
    create table #Emp2(Id int, Name varchar(20), Designation varchar(10), Teamlead int,Grp varchar(5))
    
    insert into #Emp2 values (2,'Ravi','TL',1,'A')
    insert into #Emp2 values (5,'Hari','TL',1,'A')
    insert into #Emp2 values (9,'John','TL',1,'B')
    insert into #Emp2 values (16,'David','TL',1,'C')
    
    I have relation for Team Lead and Emp:
    TeamLead Emp relation,Table Name : teamlead_emp
    
    Id  tl_id     emp_id
    1    2          4
    2    2          8
    3    5          3
    4    9          10
    5    9          11
    6    16         13
    7    16         23     
    
    
    select Name,Designation,Teamlead,Grp from #Emp1
    union all
    select Name,Designation,Teamlead,Grp from #Emp2
    union all
    select '' as Name,'' as Designation,'' as Teamlead,grp + 'Total'  as Grp from #Emp2
    group by Grp
    order by Grp,Teamlead
    
    
    Need to get OutPut Like , but above query is not getting desire output.
    
    Name	Designation	Teamlead	Grp
    Ganesh	DM	0	A
    sam	DM	0	A
    Ravi	TL	1	A
    Ram	SE	0	A
    kailash	SE	0	A
    Hari	TL	1	A
    Krishna	SE	0	A
    		0	                ATotal
    Kishora	DM	0	B
    John	TL	1	B
    Vinod	SE	0	B
    kumar	SE	0	B
    
    		0	                 BTotal
    David	TL	1	C
    Mahesh	SE	0	C
    VenkteshSE	0	C
    
    		0	                 CTotal
    Arvind	DM	0	D                
    Ramesh	SE	0	E

    Thursday, March 3, 2016 1:30 AM
  • User-219423983 posted

    Hi sureshtalla,

    You could have a look at the following code that could get the tl_Name of each Emp.

    with CTE
    as
    (
    select Id, Name,Designation,Grp from #Emp1
    --union all
    --select Id, Name,Designation,Grp from #Emp2
    union all
    select '' as Id,'' as Name,'' as Designation,grp + 'Total'  as Grp from #Emp2
    group by Grp
    )
    select c.Id, c.Name,c.Designation, te.tl_id as tl_id,
    	   (select Name from #Emp2 cc where cc.Id =te.tl_id) as tl_Name,
    	   c.Grp
    from CTE c  
    left join #teamlead_emp  te
    on c.Id = te.emp_id
    order by GRP
    

    The Output of above code is as below.

    Id	Name	Designation	tl_id	tl_Name	Grp
    15	Ganesh	DM	NULL	NULL	A
    19	sam	DM	NULL	NULL	A
    3	Krishna	SE	5	Hari	A
    4	Ram	SE	2	Ravi	A
    8	kailash	SE	2	Ravi	A
    0			NULL	NULL	ATotal
    22	Kishora	DM	NULL	NULL	B
    10	Vinod	SE	9	John	B
    11	kumar	SE	9	John	B
    0			NULL	NULL	BTotal
    13	Mahesh	SE	16	David	C
    23	Venktesh	SE	16	David	C
    0			NULL	NULL	CTotal
    14	Arvind	DM	NULL	NULL	D
    25	Ramesh	SE	NULL	NULL	E
    

    Best Regards,

    Weibo Zhang

    Thursday, March 3, 2016 6:20 AM
  • User194385433 posted

    Thanks for your reply Weibo Zhang.

    But Team Lead names are missing in the Output.

    My Output should be like: I need to show specific Team Lead users under him.

    with CTE
    as
    (
    select Id, Name,Designation,Grp from #Emp1
    union all
    select Id, Name,Designation,Grp from #Emp2
    union all
    select '' as Id,'' as Name,'' as Designation,grp + 'Total'  as Grp from #Emp2
    group by Grp
    )
    select c.Id, c.Name,c.Designation, te.tl_id as tl_id,
    	   (select Name from #Emp2 cc where cc.Id =te.tl_id) as tl_Name,
    	   c.Grp
    from CTE c  
    left join #teamlead_emp  te
    on c.Id = te.emp_id
    order by GRP
    Name	Designation	Teamlead	Grp
    Ganesh	DM	0	A
    sam	DM	0	A
    Ravi	TL	1	A
    Ram	SE	0	A
    kailash	SE	0	A
    Hari	TL	1	A
    Krishna	SE	0	A
    		0	                ATotal
    Kishora	DM	0	B
    John	TL	1	B
    Vinod	SE	0	B
    kumar	SE	0	B
    
    		0	                 BTotal
    David	TL	1	C
    Mahesh	SE	0	C
    VenkteshSE	0	C
    
    		0	                 CTotal
    Arvind	DM	0	D                
    Ramesh	SE	0	E
    

    Thanks,

    Thursday, March 3, 2016 2:56 PM
  • User-808054615 posted

    Try:

    select 
        e.Name,
        e.Designation,
        e.Teamlead,
        e.Grp,
        t.tl_id 
    from #Emp1 as e
    left join #TeamLead as t
        on t.emp_id = e.Id
        
    union all
        
    select 
        Name,
        Designation,
        Teamlead,
        Grp + case when GROUPING_ID(Name) = 0 then '' else 'Total' end,
        Id 
    from #Emp2
    group by grouping sets
        ((Name, Designation, Teamlead, Grp, Id), (Grp))
    
    order by
        Grp,
        tl_id,
        Teamlead desc
    

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 3, 2016 5:42 PM