Search code examples
sqlsql-server-2008selectcompound-index

SQL select clause with a compound

I have two lookup/reference tables (Subscribed and Unsubscribed) in my Microsoft SQL Server 2008 database with the following structure:

UserId int
PublicationId int

These fields are indexed together as a compound index.

What I want to be able to do is find all of the records that exist in the Unsubscribed table that do not have a matching record in the Subscribed table (matching UserId and PublicationId)

Functionally, I want something like:

select PublicationId, UserId
from Unsubscribed
where PublicationId, UserId not in (
   select PublicationId, UserId
   from Subscribed
)

Can anyone point me in the right direction?

Thanks.

Solution 
  • SELECT PublicationId, UserId
FROM   Unsubscribed
MINUS
SELECT PublicationId, UserId
FROM   Subscribed