asked on

sql not in

Hi,

I have a table called BAP and has a colum called MDN
I have a list of MDN which is 732 and in the database it tells me that out of that 732 there are 722 that match
I need to write a query that tells me what the 10 records are that dont match

please help

Thanks,

r8VI

sachinpatil10d

please specify 722 match is referred to same table duplicate records or any other table.

Eyal

select * from BAP where MDN not in (list of MDN seperated by comma)

R8VI

ASKER

hi sachin,

722 is the list I got doing a query IN
732 records from an excel spreadsheet

and that gives me 722 bit i need to find out of the 732 records that are in the excel sheet give me the 10 that are not in the database

Hi Eyal,

I can not do what you are suggesting as that will give me 0 records as those records are not in the DB

please help

Thanks,

R8VI

Eyal

now it's more clear :)

is it one time? if yes then use excel vlookup

if not then create temp table, add MDN from excel, and select * from #tmpBAP where MDN not in (select MDN from BAP )

R8VI

ASKER

I dont have rights to create temp table I guess excel it is lol

but then that doesnt help as I have no idea how to do a vlookup

Thanks,

R8VI

Eyal

copy list of MDN from database to new sheet in the excel

and with vlookup you can start from here

http://office.microsoft.com/en-us/excel-help/vlookup-HP005209335.aspx
or
http://www.techonthenet.com/excel/formulas/vlookup.php
google is your best friend :)

ASKER CERTIFIED SOLUTION

clamps

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pratima

you have list of MDN in comma seperated list for In clause right ?
like (1,2,3,4,5)

create this procedure

CREATE FUNCTION dbo.SplitCSV (@CSVString VARCHAR(8000), @Delimiter CHAR(1))
RETURNS @temptable TABLE (items VARCHAR(8000))
AS
BEGIN
DECLARE @pos INT;
DECLARE @slice VARCHAR(8000);
SELECT @pos = 1;
IF LEN(@CSVString) < 1 OR @CSVString IS NULL RETURN;
WHILE @pos!= 0
BEGIN
SET @pos = CHARINDEX(@Delimiter,@CSVString);
IF @pos != 0
SET @slice = LEFT(@CSVString, @pos - 1);
ELSE
SET @slice = @CSVString;

IF( LEN(@slice) > 0)

INSERT INTO @temptable(Items) VALUES (@slice);

SET @CSVString = RIGHT(@CSVString, LEN(@CSVString) - @pos);

IF LEN(@CSVString) = 0 BREAK;

END

RETURN
END

then you will try this will give you list

SELECT * FROM dbo.SplitCSV ('1,2,3,4', ',');

then try this query

SELECT Items FROM dbo.SplitCSV ('1,2,3,4', ',') where Items not in (select MDN from BAP )