select query issue

Question

Post reply

select query issue

sachinrshetty

SSCrazy

Points: 2062
More actions
May 27, 2010 at 2:50 am

#147240

Emp_id Emp_name Supervisor_id
--------------------------------------
1 A -1
2 B 1
3 C 1
4 D 2
I want to write select query which contains emp_name and his supervisor name
How to write this.
Sachin

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

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

Gopi Muluka SSCarpal Tunnel Points: 4594 More actions · Answer 1

have you heard about recursive CTE? google about it or read BOL

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

sachinrshetty (5/27/2010)
Emp_id Emp_name Supervisor_id
--------------------------------------
1 A -1
2 B 1
3 C 1
4 D 2
I want to write select query which contains emp_name and his supervisor name
How to write this.
Sachin

What you've supplied here doesn't contain a supervisor name. Please read this article[/url] on how to post questions that are easier to answer 🙂

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

sachinrshetty SSCrazy Points: 2062 More actions · Answer 3

Hi,

I want to match supervisor id with employee id itself

Kingston Dhasian SSCoach Points: 19795 More actions · Answer 4

A LEFT OUTER JOIN with the same table again will give you the desired result

SELECTEmp.Emp_Name, Sup.Emp_Name Sup_Name

FROMEmployee Emp

LEFT OUTER JOINEmployee Sup ONEmp.Supervisor_ID = Sup.Emp_ID

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 5

Kingston Dhasian (5/27/2010)
A LEFT OUTER JOIN with the same table again will give you the desired result
SELECTEmp.Emp_Name, Sup.Emp_Name Sup_Name
FROMEmployee Emp
LEFT OUTER JOINEmployee Sup ONEmp.Supervisor_ID = Sup.Emp_ID

Looking at the data in the question, Inner join will also work.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

Gopi Muluka SSCarpal Tunnel Points: 4594 More actions · Answer 6

Another way of doing this

CREATE TABLE Emp (Emp_id INT, Emp_name VARCHAR(50), Supervisor_id INT)

GO

Insert Emp

SELECT 1, 'A', -1 UNION

SELECT 2, 'B', 1 UNION

SELECT 3, 'C', 1 UNION

SELECT 4, 'D', 2

GO

WITH Emp_CTE AS (

SELECT Emp_ID, Emp_Name, CAST('NONE' AS VARCHAR(50)) as SupervisorName

FROM Emp

WHERE Supervisor_id=-1

UNION ALL

SELECT e.Emp_ID, e.Emp_Name, CAST(CTE.Emp_Name AS VARCHAR(50)) as SupervisorName

FROM Emp E

INNER JOIN Emp_CTE CTE ON CTE.Emp_ID = E.Supervisor_id

)

SELECT *

FROM Emp_CTE

GO

DROP TABLE Emp

Ujar Say Hey Kid Points: 669 More actions · Answer 7

CREATE TABLE EMPLOYEE

(

EMP_ID INT PRIMARY KEY,

EMP_NAME VARCHAR(20),

MGR_ID INT

)

INSERT INTO EMPLOYEE VALUES(1,'A',-1)

INSERT INTO EMPLOYEE VALUES(2,'B',1)

INSERT INTO EMPLOYEE VALUES(3,'C',1)

INSERT INTO EMPLOYEE VALUES(4,'D',2)

SELECT * FROM EMPLOYEE

SELECT E.EMP_NAME EmployeeName,M.EMP_NAME SupervisorName

FROM EMPLOYEE E,EMPLOYEE M

WHERE E.MGR_ID=M.EMP_ID

I hope u got the answer..

sachinrshetty SSCrazy Points: 2062 More actions · Answer 8

Thank u all guys for your help.

I will try out your logic and revert back.

Thanks a lot. 🙂

Sachin