table:employee
EID ENAME MID(manager ids)
101 rama null
102 sita 101
103 siva 101
104 ganesh 103
. . .
. . .
for 103 ID the manager ID is 101(RAMA) and for 104 manager
is SIVA
if i give employee id (EID) you have to tell the manager for
that EID write query?
eample:if i give 102 .The query output should be manager for
102 ID that it should print RAMA as output

Answers were Sorted based on User's Feedback



table:employee EID ENAME MID(manager ids) 101 rama null 102 ..

Answer / soorai ganesh

create table emp (eid int, ename varchar(50),mid int)

insert into emp values(1,'Ganesh',null)
insert into emp values(2,'Ramesh',1)
insert into emp values(3,'Suresh',1)
insert into emp values(4,'Selvam',2)
insert into emp values(5,'Vignesh',3)

select * from emp

Declare @eid int
Set @eid = 4 -- Your Input ID here..

Select 'Manager For '+CONVERT(VARCHAR,emp2.eid)+' --> '+ emp1.ename
From emp emp1
Inner join emp emp2 on emp1.eid = emp2.mid and emp2.eid = @eid

Is This Answer Correct ?    4 Yes 1 No

table:employee EID ENAME MID(manager ids) 101 rama null 102 ..

Answer / samba shiva reddy . m

select emp.ename from employee emp1
inner join employee emp
on emp1.mid=emp.eid
where emp1.eid=102

Is This Answer Correct ?    2 Yes 0 No

table:employee EID ENAME MID(manager ids) 101 rama null 102 ..

Answer / saravanan p

Declare @eid int
Set @eid=2

select 'The manager for employee id '+convert(varchar
(50),e2.eid)+' is '+e1.ename from emp e1,emp e2
where e1.eid=e2.mid and e2.eid=@eid

Is This Answer Correct ?    1 Yes 0 No

table:employee EID ENAME MID(manager ids) 101 rama null 102 ..

Answer / mohana krishna

create table #emp (eid int, ename varchar(50),mid int)

insert into #emp values(1,'Ganesh',null)
insert into #emp values(2,'Ramesh',1)
insert into #emp values(3,'Suresh',1)
insert into #emp values(4,'Selvam',2)
insert into #emp values(5,'Vignesh',3)

declare @aid int
set @aid =1
select ename from #emp
where eid = (select case isnull(mid,0) when 0 then eid
else mid end mid
from #emp
where eid=@aid
)


select m.ename from #emp e
join #emp m on (m.eid=isnull(e.mid,e.eid))
where e.eid=1

Is This Answer Correct ?    0 Yes 0 No

table:employee EID ENAME MID(manager ids) 101 rama null 102 ..

Answer / suraj

-- create table #Employees (EID int, EName varchar(20), MID
int)
-- insert #Employees values(101,'Rama',NULL)
-- insert #Employees values(102,'Sita',101)
-- insert #Employees values(103,'Shiva',101)
-- insert #Employees values(104,'Ganesh',103)

--for 103 ID the manager ID is 101(RAMA) and for 104
manager is SIVA
--Write a script which displays Shiva's Manager's name.

select a.EID, a.EName, a.MID, b.EName
from #Employees a
inner join #Employees b on a.mid=b.eid and a.EName='Shiva'

Is This Answer Correct ?    1 Yes 1 No

table:employee EID ENAME MID(manager ids) 101 rama null 102 ..

Answer / samba shiva reddy . m

this will also works for u change is id in inner join

select emp1.ename from employee emp1
inner join employee emp
on emp1.eid=emp.mid
where emp.eid=102

Is This Answer Correct ?    0 Yes 0 No

table:employee EID ENAME MID(manager ids) 101 rama null 102 ..

Answer / mohana krishna

select ename from employee
where eid = (select case mid when null then aid
else mid end mid
where eid=@aid
)


select m.name from employee e
join employee m on (m.aid=e.mid)

Is This Answer Correct ?    0 Yes 3 No

Post New Answer

More SQL Server Interview Questions

How to change the system date and time from SQL Plus Terminal ?

0 Answers   MCN Solutions,


What is factless fact table? : sql server analysis services, ssas

0 Answers  


what is unique and xaml nonclustered index

0 Answers  


What is indexing explain it with an example?

0 Answers  


Explain system views?

0 Answers  






What are the differences between substr and charindex in sql server.

0 Answers  


What are the different types of sql server replication? : sql server replication

0 Answers  


How to set the current database in ms sql server?

0 Answers  


Do you know data definition language, data control language and data manipulation language?

0 Answers  


Can I remove the default constraint columns in SQL SERVER?

5 Answers   Value Labs,


Tell me about joins in database system and explain each in detail.

0 Answers   Axtria, ITC Indian Tobacco Company,


What is a sub-query? When would you use one?

3 Answers  


Categories