Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've been given a list of employee names (several hundred), and I need to look them up, and return their employee number.

However, the names given are not necessarily exactly as they appear in the database. I therefore also need to know which ones it does not find, so we can do some further manual research to locate them and find out their employee id - but I cannot figure out how to also return the employees not found.

Example:

select name, emplid
from employees
where name in ('Smith,John','Brown,Jill','Doe,Jane')

If one of the names is 'Doe,Jane April') in the db, then I don't find her.

I am using Oracle, and I do not have the access to create tables. Is there any way to do this?

share|improve this question
    
Just change the IN to NOT IN –  FreshPhilOfSO Jan 9 at 20:18
    
Normalization should be a recurring sound here and on stackoverflow for every question with the sql tag. –  Mihai Jan 9 at 20:20
add comment

2 Answers

You can use this approach as it demonstrated in this SQL fiddle:

SQL Fiddle

Oracle 11g R2 Schema Setup:

create table cust (custname varchar2(20), custid int);
insert into cust values('Andrew', 1);
insert into cust values('John', 2);
insert into cust values('Mary', 3);

Query 1:

with names as (
select 'Andrew' AS search from dual
union all
select 'Mark' AS search from dual
union all
select 'Mary' AS search from dual
)
select n.search, NVL(c.custid, 0) custid
from names n left join cust c 
on n.search = c.custname

Results:

| SEARCH | CUSTID |
|--------|--------|
| Andrew |      1 |
|   Mary |      3 |
|   Mark |      0 |

Query 2:

with names as (
select 'Andrew' AS search from dual
union all
select 'Mark' AS search from dual
union all
select 'Mary' AS search from dual
union all
select 'Jo' AS search from dual
)
select n.search, NVL(c.custid, 0) custid,
CASE WHEN c.custid IS NULL THEN 'not found'
 WHEN c.custname = n.search THEN 'exact match'
 ELSE 'partial match' END AS result
from names n left join cust c 
on c.custname LIKE n.search || '%'

Results:

| SEARCH | CUSTID |        RESULT |
|--------|--------|---------------|
| Andrew |      1 |   exact match |
|   Mark |      0 |     not found |
|   Mary |      3 |   exact match |
|     Jo |      2 | partial match |

As you can see, you need to put the search values into the cte called names, and then left join it with your table. Using this approach will also allow you to use LIKE operator with a 'result' column, as demonstrated by the second query.

BTW, read this article on how to create a UDF that will convert the IN-list to a table

share|improve this answer
add comment

If the number is not that big you could also use:

select T.name, employees.emplid
from 
(select 'Smith,John' name from dual
 union
 select 'Smith,John' name from dual
 union
 select 'Smith,John' name from dual
) T 
left join employees on employees.name = T.name
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.