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

Envenom

Are your foreign and primary keys named exactly as mentioned? Interactions.ID vs EmailOut.ID? – Brittnybritton

Answer 1

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)

Rycca

.. 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 – Archie

Answer 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
        ) 

Commixture

Answer 3

What about

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

Hymenium