How to retrieve names within a column if values in another column corresponds to the primary key of desired name

Question

I have a table in t-sql which looks like this

personid | firstname | lastname | managerid
1        | Tom       | Bricks   | null
2        | Joe       | Liam     | 1
3        | Mary      | Hattan   | 2

I am trying to query out a table like this:

Name | ManagerName
Tom  | No manager
Joe  | Tom
Mary | Joe

I can't seem to figure out way how to query the second column out my query as it stands is like this :

SELECT 'firstname' + ' ' + 'lastname' AS 'Name

Anyone got any idea?

Are you really working with all of MySQL, SQL Server and postgresql? — jarlh, Jan 20 '15 at 14:02
possible duplicate of [Self Join to get employee manager name](http://stackoverflow.com/questions/11427300/self-join-to-get-employee-manager-name) — Mahesh, Jan 20 '15 at 14:03

HaveNoDisplayName · Accepted Answer · 2015-01-20T14:12:19.397

0

You can use Self Join to get data using two column link on the same table.

Try this

   SELECT t1.firstname as Name, ISNULL(t2.firstname,'No manager') as ManagerName
   FROM   tblName t1 
       LEFT JOIN tblName t2 
       ON t1.managerid = t2.personid

DEMO

edited Jan 20 '15 at 14:12

answered Jan 20 '15 at 14:06

HaveNoDisplayName

8,291
106
37
47

Hi thanks for the help - the problem is that the manager id corresponds to the personid in table 1. There is no second table, I am quiet confused about this. – Sizejaul Jan 20 '15 at 14:28
Yes, Right, there is no second table, thats why if you see the query, I join One table two time "tblName", just using two instance t1, and t2. This is called self join – HaveNoDisplayName Jan 20 '15 at 14:30
You can also check the DEMO link, which shows the how this query works – HaveNoDisplayName Jan 20 '15 at 14:36
Wow....thank you very much I look at it...this is very new technique...many appreciation for the tips....so this function t1. , t2. allows duplication of a specific table in a way..? – Sizejaul Jan 20 '15 at 14:47
Yes you have to have aliases so that it distinguishes between the two instnces of the table. – HLGEM Jan 20 '15 at 15:23

score 0 · Answer 2 · answered Jan 20 '15 at 14:43

You can do this by recursive CTE:

DECLARE @t TABLE
    (
      personid INT ,
      firstname NVARCHAR(MAX) ,
      lastname NVARCHAR(MAX) ,
      managerid INT
    )

INSERT  INTO @t
VALUES  ( 1, 'Tom', 'Bricks', NULL ),
        ( 2, 'Joe', 'Liam', 1 ),
        ( 3, 'Mary', 'Hattan', 2 );
WITH    cte
          AS ( SELECT   personid ,
                        firstname ,
                        lastname ,
                        managerid
               FROM     @t
               WHERE    ManagerID IS NULL
               UNION ALL
               SELECT   t.personid ,
                        t.firstname ,
                        t.lastname ,
                        t.managerid
               FROM     @t t
                        INNER JOIN cte ecte ON ecte.personid = t.ManagerID
             )
    SELECT  *
    FROM    cte
GO

Output:

personid    firstname   lastname    managerid
1   Tom Bricks  NULL
2   Joe Liam    1
3   Mary    Hattan  2

How to retrieve names within a column if values in another column corresponds to the primary key of desired name

2 Answers2