CODEWITHSUNDEEP
sqlsql-server
crow_doe
crow_doe

Reputation: 43

SQL Server - Exclude Records from other tables

I used the search function which brought me to the following solution.

Starting Point is the following: I have one table A which stores all data. From that table I select a certain amount of records and store it in table B.

In a new statement I want to select new records from table A that do not appear in table B and store them in table c. I tried to solve this with a AND ... NOT IN statement. But I still receive records in table C that are in table B.

Important: I can only work with select statements, each statement needs to start with select as well. Does anybody have an idea where the problem in the following statement could be:

Select *
From
(Select TOP 10000 * 
FROM [table_A]
WHERE Email like '%@domain_A%' 
AND Id NOT IN (SELECT Id
FROM [table_B])) 
Union
(Select TOP 7500 * 
FROM table_A] 
WHERE Email like '%@domain_B%'
AND Id NOT IN (SELECT Id
FROM [table_B]))
Union
(SELECT TOP 5000 * 
FROM [table_A] 
WHERE Email like '%@domain_C%'  
AND Id NOT IN (SELECT Id
FROM [table_B]))

Upvotes: 0

Views: 77

Answers (3)

crow_doe
crow_doe

Reputation: 43

Thank you guys all for your feedback, from which I learned a lot. I was able to fix the statement with your help. Above is the statement which is working now with the desired results:

Select Id
From
(Select TOP 10000 * FROM Table_A
WHERE Email like '%@domain_a%'
AND Id NOT IN (SELECT Id
FROM Table_B)
order by No desc) t1
Union
Select Id
From
(Select TOP 7500 * FROM Table_A
WHERE Email like '%@domain_b%'
AND Id NOT IN (SELECT Id
FROM Table_B)
order by No desc) t2
Union
Select Id
From
(SELECT TOP 5000 * FROM Table_A
WHERE Email like '%@domain_c%'  
AND Id NOT IN (SELECT Id
FROM Table_B)
order by No desc) t3

Upvotes: 0

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

Try NOT EXISTS instead of NOT IN

SELECT
*
FROM TableA A
  WHERE NOT EXISTS
  (
     SELECT 1 FROM TableB WHERE Id = A.Id
  )

Upvotes: 2

user8513344
user8513344

Reputation:

So Basically the idea here is to select everything from table A that doesnt exists in table B and Insert all that into Table C? 

    INSERT INTO Table_C 
    SELECT a.colum1, a.column2,......
    FROM [table_A]
    LEFT JOIN [table_B] ON a.id = b.ID
    WHERE a.Email like '%@domain_A%' AND b.id IS NULL

Upvotes: 0

Related Questions