tSQL NOT IN Query

sql sql-server tsql notin
cihadakt picture cihadakt · May 28, 2013 · Viewed 31.7k times · Source

I want to get the ID's of [interactions] table but these ID's must not equal to [EmailOUT] table. I couldn't write the query.

Select ID from EmailOut         
where ID NOT IN         
   (select ID from
    [172.28.101.120].[GenesysIS].dbo.interactions 
    where media_type = 'email'
    and type = 'Outbound')

something similar to this. I want Outbound Emails in Interactions table but these emails may exist in EmailOut table. I want to remove them. Outbound Email count about 300 but this query result should less than 300

Answer

Andrey Gordeev picture Andrey Gordeev · May 28, 2013

It seems you should reverse your query, if you want to get the ID's of [interactions] table:

select ID from
[172.28.101.120].[GenesysIS].dbo.interactions 
where media_type = 'email'
and type = 'Outbound'
AND ID NOT IN (SELECT ID FROM EmailOut)

Related questions

NULL values inside NOT IN clause

This issue came up when I got different records counts for what I thought were identical queries one using a not in where constraint and the other a left join. The table in the not in constraint had one null …

Read More
How to write "not in ()" sql query using join

Could some one please provide how to write following sql query using joins. I do not want use not in as well as if possible I would like to replace where condition as well. SELECT d1.Short_Code FROM domain1 …

Read More
SQL Server - NOT IN

I need to build a query that will show me records that are in Table 1, but that are not in Table 2, based on the make-model-serial number combination. I know for fact that there are 4 records that differ, but my query …

Read More