tSQL NOT IN Query

Question

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

Are your foreign and primary keys named exactly as mentioned? Interactions.ID vs EmailOut.ID? — madC, May 28 '13 at 06:57

score 17 · Accepted Answer · answered May 28 '13 at 06:53

17

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)

answered May 28 '13 at 06:53

Andrey Gordeev

30,606
13
135
162

.. and you can also use the NOT IN without selecting, like this example snip where you check for integers : not in (119, 138, 158, 165) Just to mention a way to test the operator – netfed Jun 19 '17 at 11:56

Devart · Answer 2 · 2013-05-28T07:05:22.647

2

Try this one -

SELECT t2.*
FROM [172.28.101.120].[GenesysIS].dbo.interactions t2
WHERE t2.media_type = 'email'
    AND t2.[type] = 'Outbound'
    AND NOT EXISTS (
            SELECT 1 
            FROM dbo.EmailOut t 
            WHERE t.id = t2.id
        )

edited May 28 '13 at 07:05

answered May 28 '13 at 06:59

Devart

119,203
23
166
186

score -1 · Answer 3 · answered May 28 '13 at 06:58

-1

What about

select ID from [172.28.101.120].[GenesysIS].dbo.interactions 
where media_type = 'email'
and type = 'Outbound' 
minus
select ID from EmailOut

answered May 28 '13 at 06:58

just.do.it

69
1
3

tSQL NOT IN Query

3 Answers3