CODEWITHSUNDEEP
sql-server-2005
Tintin
Tintin

Reputation: 2983

How to use "IN" for more than one column

This question might be trivial or even silly but I was wondering if there is a way to use "IN" on more than one column on one to one matching. For example I use 

select emp_id from employee where emp_id IN (select emp_id from employee_other)

How could I achieve something like

select emp_id from employee where emp_id,emp_org IN (select emp_id,emp_org from employee_other)

I know I cant be using the following because it will simply do the union whereas I want a selection based on one to one record matching.

select emp_id from employee where emp_id IN (select emp_id from employee_other) and emp_org in (select emp_org from employee)

Please note that I am reluctant to use EXCEPT.

Thanks guys

Upvotes: 3

Views: 307

Answers (5)

amit_g
amit_g

Reputation: 31270

Use Inner Join

select e1.emp_id from employee  e1
inner join employee_other e2 on e1.emp_id = e1.emp_id and e1.emp_org = e2.emp_org

You may have to use Distinct in case the employee_other table causes dups.

select Distinct e1.emp_id from employee  e1
inner join employee_other e2 on e1.emp_id = e1.emp_id and e1.emp_org = e2.emp_org

Upvotes: -1

Lasse V. Karlsen
Lasse V. Karlsen

Reputation: 391734

IN in Microsoft SQL Server only works with a single column, ie. you can only write X IN (...), never anything remotely like X,Y IN (...).

There are two ways to handle this, depending on your data:

  • Joining with a sub-query
  • Using EXISTS

To JOIN, do this:

select emp_id
from employee
    inner join (select emp_id,emp_org from employee) as x
    on employee.emp_id = x.emp_id and employee.emp_org = x.emp_org

Your example is a bit lousy, however, since you're using the same table.

To use EXISTS, do this:

select emp_id
from employee
where exists (
    select emp_id,emp_org from employee e2
    where e2.emp_id = employee.emp_id and e2.emp_org = employee.emp_org)

This, in the same way as the join, links the main table to the "sub-query" table, but whereas the join will produce duplicate rows if the "sub-query" produces multiple hits, the EXISTS clause will not.

Upvotes: 3

Chris Allwein
Chris Allwein

Reputation: 2578

You had it almost completely right in your second example. You just need to add parens around your column names.

select emp_id 
from employee 
where (emp_id,emp_org) IN (select emp_id,emp_org from employee)

Upvotes: -1

SQLMenace
SQLMenace

Reputation: 135181

I don't understand what you are trying to accomplish with emp_org in (select emp_org from employee) isn't that always true?

does this work?

select emp_id from employee e
where exists (select 1 from employee_other eo  
              WHERE e.emp_id =eo.emp_id and 
              AND e.emp_org = eo.emp_org )

Upvotes: -1

bobs
bobs

Reputation: 22224

You may want to use the EXISTS operator

select e.emp_id 
from employee e
where EXISTS
    (
    SELECT *
    FROM employee_other eo
    WHERE e.emp_id = eo.emp_id
        AND e.emp_org = eo.emp_org
    )

Upvotes: 5

Related Questions