| Author |
 Topic  |
|
|
dildileep
Starting Member
3 Posts |
 Posted - 06/09/2012 : 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 ?
|
Edited by - dildileep on 06/09/2012 15:10:38
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47189 Posts |
Posted - 06/09/2012 : 15:11:51
|
select student_id from student_db where student_id not in('12346','1245',45612',..........)
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 - 06/09/2012 : 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
India
47189 Posts |
Posted - 06/09/2012 : 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
213 Posts |
Posted - 06/10/2012 : 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 - 06/11/2012 : 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
India
47189 Posts |
Posted - 06/12/2012 : 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
India
47189 Posts |
|
| |
Topic |
|
displaying records not present in the condition
