SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Remove least paid employee who are reporting to BL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ashwanth
Starting Member

India
1 Posts

Posted - 04/06/2014 :  08:44:42  Show Profile  Reply with Quote
Remove least paid employee who are reporting to BLAKE ?
my solution
delete emp where sal = (select min(sal) from emp where mgr =
(select empno from emp where ename = 'BLAKE')
the correct solution is
delete emp where sal = (select min(sal) from emp where mgr =
(select empno from emp where ename = 'BLAKE')) and
ename in(select ename from emp where mgr =
(select empno from emp where ename = 'BLAKE'))
here the question why my solution is wrong
1)find out who is the least paid employee who are reporting to BLAKE
(select min(sal) from emp where mgr =
(select empno from emp where ename = 'BLAKE')
2)Remove least paid employee who are reporting to BLAKE ?
my solution
so ,
delete emp where sal = (select min(sal) from emp where mgr =
(select empno from emp where ename = 'BLAKE')
--1 row deleted
delete emp where sal = (select min(sal) from emp where mgr =
(select empno from emp where ename = 'BLAKE')) and
ename in(select ename from emp where mgr =
(select empno from emp where ename = 'BLAKE'))
----1 row deleted

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 04/19/2014 :  12:37:08  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
You would be deleting all employees that have the same salary as the min salary of those reporting to Blake. So if they have that salary then they would be deleted whether or not the report to Blake.

Your solution happens to work because there is only one employee with that salary.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000