1

I need some help writing an SQL statement for the below requirement.

I have list of employee_id which I need to check whether they are exist in the DB or not from the java layer I want to use one query for this.

Sample query:

SELECT *
FROM employee
WHERE employee_id IN (1001,1002,1002,10000004).

In this query 10000004 does not exist in DB.

One approach in my mind is to use the below query: 

SELECT Count(employee_id)
FROM employee
WHERE employee_id IN (1001,1002,1002,10000004).

Then check the list size and the result from the query in java layer. But I don’t want this because I need all those employee_id which does not exist in DB.

Newd
  • 2,174
  • 2
  • 17
  • 31
Ankur Agarwal
  • 9
  • 2
  • That seems like it would be the only way to go. I don't see any other way to determine that a given employee_id is not in the db. I don't know of a way to return an empoyee_id that is not in the return result. I don't think you need to do a count, however. You could simply do the first select statement that you have listed and then spin through the results with each employee_id. – JustAspMe Jun 26 '15 at 14:00
  • if you have a full list of all possible ids in some table, you can find out which one is not in the specified table. if you only have the ids in your app, you can pull the ids already in the specified table and compare the full list to see who is missing. – Tim3880 Jun 26 '15 at 14:04
  • Hi Ankur, Please see my answer below. This might help you. The idea is to create a temp table from the comma seperated list of employee ids and then using left join to get ids not in db. The query was run in MS SQL Server. – hpatel Jun 26 '15 at 14:16

3 Answers3

0
declare @employeeids varchar(1000) --to store ids as comma seperated string
declare @tmpEmployee table (employee_id varchar(50)) --temp employee table to store ids from string
declare @pointer int

select  @employeeids = '1001,1002,1002,10000004' --list of ids to check against database

while (charindex(',', @employeeids, 0) > 0)
begin
        set @pointer =   charindex(',', @employeeids, 0)      
        insert into   @tmpEmployee (employee_id)

        --remove white spaces if exists
        select ltrim(rtrim(substring(@employeeids, 0, @pointer)))   
        set @employeeids = stuff(@employeeids, 1, @pointer,   '') 
end

insert into   @tmpEmployee (employee_id)
select ltrim(rtrim(@employeeids))


select   r.employee_id -- required ids which does not exists in database
        ,e.employee_id 
from    @tmpEmployee r 
            left join employee e on r.employee_id=e.employee_id
where   e.employee_id is null
hpatel
  • 199
  • 8
0

If you are using Oracle, then this link may help you. It's all about usage of built in SYS.DBMS_DEBUG_VC2COLL function

Community
  • 1
  • 1
Ashish Patil
  • 4,428
  • 1
  • 15
  • 36
0

Exist a very bad way to do that is this:

    SELECT * FROM (SELECT 5930 id UNION SELECT 8109 id 
UNION SELECT 8110 id UNION SELECT 8115 id UNION SELECT 8112 id 
UNION SELECT 8113 id UNION SELECT -1 id) b
    WHERE b.id NOT IN (SELECT f.id FROM employee f)

I recommed you do that in other way.

maframaran
  • 170
  • 9