Home » SQL & PL/SQL » SQL & PL/SQL » Multiple Column Subquery restricting rows (Oracle 9i)
Multiple Column Subquery restricting rows [message #396035] Fri, 03 April 2009 10:17 Go to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
In the below subquery, I want to exclude the rows in the output where job_id, salary occurs only once that is employee_id 100 but it has to be included in condition.
How do i write it?

select employee_id, job_id, salary, department_id
from employees
where (department_id, salary) in (select department_id, salary
from employees
where employee_id in (100, 102, 150));

EMPLOYEE_ID JOB_ID         SALARY DEPARTMENT_ID
----------- ---------- ---------- -------------
        150 SA_REP          10000            80
        169 SA_REP          10000            80
        156 SA_REP          10000            80
        101 AD_VP           17000            90
        102 AD_VP           17000            90
        100 AD_PRES         24000            90

6 rows selected.
Re: Multiple Column Subquery restricting rows [message #396041 is a reply to message #396035] Fri, 03 April 2009 10:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to better explain your requirement, for instance given an example: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Multiple Column Subquery restricting rows [message #396064 is a reply to message #396035] Fri, 03 April 2009 12:31 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
having count(*) = 1
Re: Multiple Column Subquery restricting rows [message #396100 is a reply to message #396041] Fri, 03 April 2009 20:21 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
I want to find out is any employee other than these employees(100, 102, 150) is earning the same salary and in the same department? So in the output, it says that for employee_id 100 there are only one person earning the same salary that is employee 100, so I need to exclude this row because there are not employee other than 100.

Output should be like this.

EMPLOYEE_ID JOB_ID         SALARY DEPARTMENT_ID
----------- ---------- ---------- -------------
        150 SA_REP          10000            80
        169 SA_REP          10000            80
        156 SA_REP          10000            80
        101 AD_VP           17000            90
        102 AD_VP           17000            90


If you see the output there are employees who ears the same salary and in same department along with the specified employees.

Please give me the query using Multiple Column Subquery. Thanks
Re: Multiple Column Subquery restricting rows [message #396101 is a reply to message #396035] Fri, 03 April 2009 20:27 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Post DDL for tables.
Post DML for test data.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Re: Multiple Column Subquery restricting rows [message #396114 is a reply to message #396100] Sat, 04 April 2009 01:10 Go to previous messageGo to next message
danish_fsd@yahoo.com
Messages: 38
Registered: February 2008
Location: Pakistan
Member
Hi,

Hope following will help you.

SELECT employee_id, job_id, salary, department_id
  FROM employees
 WHERE (department_id, salary) IN (
          SELECT   department_id, salary
              FROM employees
             WHERE (department_id, salary) IN (
                                          SELECT department_id, salary
                                            FROM employees
                                           WHERE employee_id IN
                                                              (100, 102, 150))
          GROUP BY department_id, salary
            HAVING COUNT (*) > 1);


Regards

Danish
Re: Multiple Column Subquery restricting rows [message #398271 is a reply to message #396035] Wed, 15 April 2009 20:15 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
In the below subquery, the rows are repeating for job_id, salary and department_id wherein I need to retrieve any one row where repeating. How do i write it?

select employee_id, job_id, salary, department_id
from employees
where (department_id, salary) in (select department_id, salary
from employees
where employee_id in (102, 150));

EMPLOYEE_ID JOB_ID         SALARY DEPARTMENT_ID
----------- ---------- ---------- -------------
        150 SA_REP          10000            80
        169 SA_REP          10000            80
        156 SA_REP          10000            80
        101 AD_VP           17000            90
        102 AD_VP           17000            90
        
6 rows selected.



My ouptut should be like this


EMPLOYEE_ID JOB_ID         SALARY DEPARTMENT_ID
----------- ---------- ---------- -------------
        150 SA_REP          10000            80
        101 AD_VP           17000            90
       
--OR

EMPLOYEE_ID JOB_ID         SALARY DEPARTMENT_ID
----------- ---------- ---------- -------------
        
        169 SA_REP          10000            80
        102 AD_VP           17000            90

Re: Multiple Column Subquery restricting rows [message #398272 is a reply to message #396035] Wed, 15 April 2009 20:19 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You really should be doing these homework assignments yourself.
Re: Multiple Column Subquery restricting rows [message #398286 is a reply to message #398271] Wed, 15 April 2009 23:09 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at ROW_NUMBER function.

Regards
Michel
Previous Topic: Need help formatting output from a SQL search
Next Topic: query help
Goto Forum:
  


Current Time: Sat Dec 10 04:47:33 CST 2016

Total time taken to generate the page: 0.15227 seconds