February 13, 2016 at 6:32 pm
Hi,
Table Emp:
Id Name Designation Teamlead
2 Ravi TL 1
3 Krishna SE 0
4 Ram SE 0
5 Hari TL 1
8 kailash SE 0
9 John TL 1
10 Vinod SE 0
11 kumar SE 0
Table 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
2 Ravi TL 1
10 Vinod SE 0
11 Kumar SE 0
5 Hari TL 1
4 Ram SE 0
9 John TL 1
3 Krishna SE 0
8 Kailash SE 0
Thanks
February 13, 2016 at 7:14 pm
What you have tried so far? Thanks for showing your desired result and some info about the source data. So I can provide tested code, please provide a CREATE TABLE statement and some INSERT statements so I can setup the environment on my local instance.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 14, 2016 at 6:07 pm
suresh0534 (2/13/2016)
Hi,Table Emp:
Id Name Designation Teamlead
2 Ravi TL 1
3 Krishna SE 0
4 Ram SE 0
5 Hari TL 1
8 kailash SE 0
9 John TL 1
10 Vinod SE 0
11 kumar SE 0
Table 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
2 Ravi TL 1
10 Vinod SE 0
11 Kumar SE 0
5 Hari TL 1
4 Ram SE 0
9 John TL 1
3 Krishna SE 0
8 Kailash SE 0
Thanks
So close to being a great post but you missed a couple of things. If you take a look at the first link under "Helpful Links" in my signature line below, you'll see what I mean. As that article states, you really need to set your data up in a readily consumable fashion. All you were missing was a couple of quotes, commas, a SELECT, and a UNION all on each line of data and you'd almost be there.
Here's one way to post readily consumable data for your post... please try to do it one way or the other for future posts to get the best help more quickly.
--===== If the test tables already exist,
-- drop them to make reruns in SSMS easier.
IF OBJECT_ID('tempdb..#emp','U') IS NOT NULL
DROP TABLE #emp
;
IF OBJECT_ID('tempdb..#teamlead_emp','U') IS NOT NULL
DROP TABLE #teamlead_emp
;
--===== Create and populate the test tables on the fly.
SELECT d.*
INTO #emp
FROM (
SELECT 2 ,'Ravi' ,'TL',1 UNION ALL
SELECT 3 ,'Krishna','SE',0 UNION ALL
SELECT 4 ,'Ram' ,'SE',0 UNION ALL
SELECT 5 ,'Hari' ,'TL',1 UNION ALL
SELECT 8 ,'kailash','SE',0 UNION ALL
SELECT 9 ,'John' ,'TL',1 UNION ALL
SELECT 10,'Vinod' ,'SE',0 UNION ALL
SELECT 11,'kumar' ,'SE',0
) d (Id,Name,Designation,Teamlead)
;
SELECT d.*
INTO #teamlead_emp
FROM (
SELECT 1,2,10 UNION ALL
SELECT 2,2,11 UNION ALL
SELECT 3,5,4 UNION ALL
SELECT 4,9,3 UNION ALL
SELECT 5,9,8
) d (Id,tl_id,emp_id)
;
After that, (and do see the warnings after this), we just need an rCTE (Recursive CTE) to solve this problem. Sure, it's only two levels deep now and could have been solved another way but if they want to use it for other parts of the company, they'll be able to (after a minor change covered in the warnings).
Here's the code...
WITH cteHierarchy AS
(
SELECT Id = Id
,SortOrder = CAST(CAST(Id AS BINARY(4)) AS VARBINARY(100))
FROM #emp
WHERE Designation = 'TL'
UNION ALL
SELECT Id = tle.emp_id
,SortOrder = CAST(cte.SortOrder + CAST(tle.emp_id AS BINARY(4)) AS VARBINARY(100))
FROM cteHierarchy cte
JOIN #teamlead_emp tle ON tle.tl_id = cte.Id
)
SELECT e.*
FROM cteHierarchy h
JOIN #emp e ON h.Id = e.Id
ORDER BY SortOrder
;
... and here are the warnings... just in case your real data looks like the test data you provided... 😉
1. You've stored essential hierarchical information in the #Emp table instead of where it belongs... in the positional table that you call teamlead_emp. There is not need tor the TEAMLEAD column in the Emp table. It's also duplicate data because a "Designation" of "TL" should suffice to identify the team leaders.
2. The teamlead_emp table is missing data. It should have the team leaders listed as emp_id's along with their boss as the tl_id, etc, etc, up the chain of command where the single big boss would have a tl_id of NULL. There should be a "dog eared" self-referencing FK that prevents someone from appearing in the tl_id column unless they first appear in the emp_id column. Of course, there should also be an FK on the emp_id column back to the ID column in the Emp table, as well.
--Jeff Moden
February 14, 2016 at 6:11 pm
p.s. The VARBINARY(100) is good out to 25 levels. Since a Boeing 747 has a parts list that's only about 18 levels deep, that should suit just about anything you want to do with your company unless you're running an MLM.
--Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply