Jayan

• Mar 8th, 2007

 

It is very simple ......
select * from table where salary=(select salary from table where empno=20)

Nikhil_4_Oracle

• Mar 8th, 2007

 

HI ALL,

This will work,

Select * from emp where

sal In (select sal from emp where empno=20)

Minus

Select * from emp where empno=20;

Regards,

Nikhil

brl51274

 Profile Answers by brl51274 Questions by brl51274

• Mar 9th, 2007

 

Ur query returns empno 20 record as well,which I don't want. Query should return all records except empno20.

nachs

• Mar 14th, 2007

 

select * from emp where sal = (select sal from emp where empno = 20) and empno <> 20

ora.nachs

 Profile Answers by ora.nachs 

• Mar 14th, 2007

 

select * from emp where sal = (select sal from emp where empno = 20) and empno <> 20

mala

• Mar 17th, 2007

 

Hi All,

The query is supposed to return the rows other than empno 20

SELECT * FROM emp WHERE sal  NOT IN  (SELECT sal FROM emp WHERE empno=20);

Regards,
Mala

G.Deepthi

• Mar 17th, 2007

 

To retrieve all row except eno = 20 and  on basics of salary earning by eno =
20 then 

SELECT * FROM e WHERE sal IN (SELECT sal FROM e WHERE eno = 20) AND eno !=
20;
 

M.R.Pavan Kumar

• Mar 21st, 2007

 

Hi,

Check this query. It will also work for the requirement.

Select * emp
minus
select * from emp where deptno=20;

kirandstyle

 Profile Answers by kirandstyle 

• May 5th, 2008

 

Hi dude,
if suppose there is a Jobhistory table which contains the following columns in it
say...empno,ename,salary,startdate,enddate,position

here startdate is the date where an employee starts his new job
and enddate is nothing but which determines if the employee is still doing a job or not.
so in this context to your question we may have 2 queries possibly.

a)
select x.* from jobhistory x
where x.salary in (select y.salary from jobhistory y
where y.empno=20)
and x.empno<>20

The above query returns the employees matching the salary of employee 20 irrespective of the current sitution. i.e., comparision to the past salary which the employee has worked for and also comparision with the new salary what the employee is working for now..
so u might have multiple salaries of employee 20 which are compared against different employees in the job history table

b)
select x.* from jobhistory x
where x.salary in(select y.salary from jobhistory y
where y.empno=20
and y.enddate is null)
and x.enddate is null
and x.empno<>20

This second query returns the employees who salary is matching to employee 20
with his present salary.
This query also returns only those employees who are currently working and whose salaries are matching to employee 20 in his current job


I would really appreciate if your comments on this.

deepikazain

 Profile Answers by deepikazain Questions by deepikazain

• Feb 27th, 2010

 

SELECT * FROM emp WHERE sal NOT IN (SELECT sal FROM emp WHERE empno=20)