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
Answer Posted / 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 |
Post New Answer View All Answers
What is a trigger in sql server?
Do you know what is rank function?
What is clustered index
What is the difference between a function and a trigger?
What happens if ntwdblib.dll is missing on your machine?
Write a program to fetch first 10 records from a file?
What is sql injection and why is it a problem? : sql server security
What do you mean by recursive stored procedure?
What are the advantages of using stored procedures?
What causes index fragmentation?
You want to check the syntax of a complicated update sql statement without executing it. What command should you use?
Explain syntax for viewing, dropping and disabling triggers?
Does a sql server 2005 select statement require a from?
How to delete an existing row with delete statements in ms sql server?
This question asked during interview, 2) At the end of each month, a new table is created for each bank that contains monthly metrics consolidated at the account level. The table naming convention is bankX_YYYYMM where X represents the numeric designation of the bank and YYYYMM indicates the 4 digit year and 2 digit month. The tables contain the following fields: name data type description account text account number registered boolean indicates whether the account is registered num_trans integer number of transactions made during the time period spend numeric(9,2) total spend during the time period a) Write a SQL query that will display the total number of transactions and total spend for "Bank1" during the 4th quarter of 2009. b) Write a SQL query that will display the total number of transactions and total spend at "Bank1" and "Bank2", broken out by registered vs. non-registered accounts, during January 2010 not sure what is correct answer and how to solve?