i am geeting an idea

any one please help me
In our company we maitain a table for cheques issued for
example the description is
cheques_issued(chq_no int, reciever_name varchar(30))
we ha ve so many number of records in this table with
defferent chq numbers.
one day our senio manager came to me and given a list of
100 numbers collected from the recievers and asked me to
find out how many of the numbers in the list of 100 given
by him are not there in the table.
is there any method to find it direectly trough a querry?
if i create a temp table i hve to use 100 insert
statements to send 100 values into temparary table and
compare the temp table with cheques_issued table. This is
problematic for me.
please give me any solution.Once you have your 100 numbers in a #temp table (called #temp100 here),
there are plenty of ways. Let's assume this DDL and sample data:
CREATE TABLE cheques_issued(chq_no INT)
INSERT cheques_issued VALUES(1)
INSERT cheques_issued VALUES(2)
INSERT cheques_issued VALUES(3)
INSERT cheques_issued VALUES(4)
CREATE TABLE #temp100(chq_no INT)
INSERT #temp100 VALUES(1)
INSERT #temp100 VALUES(2)
INSERT #temp100 VALUES(3)
INSERT #temp100 VALUES(4)
INSERT #temp100 VALUES(5)
INSERT #temp100 VALUES(6)
INSERT #temp100 VALUES(7)
You could get your results using a simple NOT IN
SELECT COUNT(*) FROM #temp100 WHERE chq_no NOT IN (SELECT chq_no FROM
cheques_issued)
Or a NOT EXISTS
SELECT COUNT(*) FROM #temp100 WHERE NOT EXISTS (SELECT chq_no FROM
cheques_issued WHERE chq_no = #temp100.chq_no)
Or a LEFT JOIN
SELECT COUNT(*) FROM #temp100
LEFT OUTER JOIN cheques_issued c
ON #temp100.chq_no = c.chq_no
WHERE c.chq_no IS NULL
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"kalyana chakravarthi" <kalyannow@.yahoo.com> wrote in message
news:05d001c3c069$3a4a6900$a401280a@.phx.gbl...
> any one please help me
> In our company we maitain a table for cheques issued for
> example the description is
> cheques_issued(chq_no int, reciever_name varchar(30))
> we ha ve so many number of records in this table with
> defferent chq numbers.
> one day our senio manager came to me and given a list of
> 100 numbers collected from the recievers and asked me to
> find out how many of the numbers in the list of 100 given
> by him are not there in the table.
> is there any method to find it direectly trough a querry?
> if i create a temp table i hve to use 100 insert
> statements to send 100 values into temparary table and
> compare the temp table with cheques_issued table. This is
> problematic for me.
> please give me any solution.
>