[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 445
  • Last Modified:

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
0
R8VI
Asked:
R8VI
1 Solution
 
sachinpatil10dCommented:
please specify  722 match is referred to same table duplicate records or any other table.
0
 
EyalCommented:
select * from BAP where MDN not in (list of MDN seperated by comma)
0
 
R8VIAuthor Commented:
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
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
EyalCommented:
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 )
0
 
R8VIAuthor Commented:
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
0
 
EyalCommented:
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 :)
0
 
clampsCommented:
Copy your 732 excel entries in a text editor, replace the linebreaks with
' AS XY UNION SELECT '
(include the inverted commas)
put
SELECT '
in front of the replaced text so that you have something similar like:
SELECT 'value1' AS XY UNION SELECT 'value2' AS XY UNION SELECT 'value3' AS XY...
Now create a query on the tabel BAP like this:
SELECT XYZ.XY
FROM BAP
RIGHT OUTER JOIN (SELECT 'value1' AS XY UNION SELECT 'value2' AS XY UNION SELECT 'value3' AS XY...) AS XYZ ON BAP.MDN=XYZ.XY
WHERE BAP.MDN IS NULL

replace the SEELCT statement in the brakets with the one you did in your text editor....
don't use inverted commas if you're looking for numbers.
cheers
0
 
Pratima PharandeCommented:
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 )

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now