SQLTeam.com | Weblogs | Forums

Need to help sql server query

sql2008
sql2012

#1
Hi I have doubt in sql server

Table: Emp

empid | empidref | Name
 1    |  NULL    |  a
 3    |   1      |  b
 4    |   2      |  c
 2    | NULL     |  d
 5    | NULL     |  e
 6    | NULL     |  f
 8    |  7       |  g 
 7    | NULL     |  h
10    |NULL      |aa
I want merge empidref values in empid when empid also same value. if empid values avilable in empidref table then same empidref values consider as empid and corresponding empidref name need retrive if empid values not avilable in empidref table then same empid values consider as empid and corresponding name need retrive
Based on above table I want output like below

Empid  | Name
1      | b
2      | c
5      | e
6      | f
7      |g
10     |aa


I tried like below
SELECT e1.empid, e2.Name
FROM Emp e1 INNER JOIN Emp e2
    ON e1.empid = e2.empidref
WHERE e1.empidref IS NULL
but is not given expected result
please tell me how to write query to achive this task in sql server

#2
DECLARE  @Emp TABLE
    ([empid] int, [empidref] varchar(4), [Name] varchar(2));
    
INSERT INTO @Emp
    ([empid], [empidref], [Name])
VALUES
    (1, NULL, 'a'),
    (3, '1', 'b'),
    (4, '2', 'c'),
    (2, NULL, 'd'),
    (5, NULL, 'e'),
    (6, NULL, 'f'),
    (8, '7', 'g'),
    (7, NULL, 'h'),
    (10, NULL, 'aa');

This is the query:

SELECT
    E1.EmpID
    ,E2.Name
FROM
    @Emp AS E1
    INNER JOIN @Emp AS E2
        ON (E1.empid = E2.EmpIdRef 
            AND E2.empidref IS NOT NULL)

            OR

            (E1.empid = E2.EmpId 
             AND E2.empidref IS NULL 
             AND NOT EXISTS(SELECT * FROM @Emp AS E3 WHERE E3.EmpIdRef = E1.empid));

Output:

EmpID       Name
1           b
2           c
5           e
6           f
7           g
10          aa

#3

Another solution, same output:

;WITH CTE
AS(SELECT EmpID
     ,EmpIdRef
     ,Name
     ,ROW_NUMBER()OVER(PARTITION BY COALESCE(EmpIDRef,EmpID) ORDER BY EmpIDRef DESC, EmpID) AS RN
    FROM @Emp)

SELECT 
    E1.EmpID
    ,E2.Name    
FROM 
    CTE AS E1
    INNER JOIN CTE AS E2
        ON E1.EmpID = COALESCE(E2.EmpIDRef,E2.EmpID)
WHERE
    E2.RN = 1

Output:

EmpID       Name
1           b
2           c
5           e
6           f
7           g
10          aa