displaying records not present in the condition

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

displaying records not present in the condition

Author

Topic

dildileep
Starting Member

3 Posts

Posted - 2012-06-09 : 15:10:12

I have list of student ids of around 2000 students o be searched in a table. I would write the query as

select student_id from student_db where student_id in('12346','1245',45612',..........)

this query displays the ids which are present, but i need the student ids which are not present in the given set of ids. Can that be accomplished by a sql query ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-09 : 15:11:51

[code]
select student_id from student_db where student_id not in('12346','1245',45612',..........)
[/code]

another way is to dump id values to a temporary table and use left join or not exists

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dildileep
Starting Member

3 Posts

Posted - 2012-06-09 : 15:22:00

I dont want to create another temporary table for it. we get return code whether a condition is success or not. Is there any way to write sql such that we get return code for every id that is searched ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-09 : 15:37:44

quote:
Originally posted by dildileep

I dont want to create another temporary table for it. we get return code whether a condition is success or not. Is there any way to write sql such that we get return code for every id that is searched ?

do you mean you've to return code even if id doesnt exist in table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sshelper
Posting Yak Master

216 Posts

Posted - 2012-06-10 : 23:38:36

If I understood your question correctly, one way to do this is to first load your list of student IDs in a table variable (not a temporary table). Then given this table variable, you can return a flag for each student ID displaying whether it exists in your table or not. Your query will look like the following, assuming the name of your table variable is @StudentIDs.

SELECT A.[Student_ID], CASE WHEN B.[Student_ID] IS NOT NULL THEN 1 ELSE 0 END AS [StudentIDExists]
FROM @StudentIDs A LEFT OUTER JOIN [dbo].[Student_DB] B
ON A.[Student_ID] = B.[Student_ID]

Hope this helps.

SQL Server Helper.
http://www.sql-server-helper.com/error-messages/msg-1-500.aspx

dildileep
Starting Member

3 Posts

Posted - 2012-06-11 : 23:27:24

@visakh16.. yes even returning code if not present would also be fine. So that I can process using condition later or else displaying the record not present

@sshelper: your solution looks good, i have never tried using temporary variables will try this one once.

I found a query like the below

select 1234,234,456 from dual not exists in (Select student_id from
studentdb where student_id in(1234,234,456)

but this query is wrking when only one id is given in the first part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-12 : 15:50:19

you can do like below for that


SELECT t.id,
CASE WHEN s.student_id IS NOT NULL THEN 1 ELSE 0 END AS Is_Present
FROM
(
VALUES ('12346'),('1245'),(45612')
) AS t (id)
LEFT JOIN student_db s
ON s.student_id = t.id

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-12 : 15:51:42

See below link to understand some of the applications of VALUES table constructor in sql 2008

http://visakhm.blogspot.com/2012/05/multifacet-values-clause.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources