SQL - using IN over tables joined in several rows

Question

Good. I have a small set of installation tables. I will just give an example because this is the best way to report this problem.

Certificate Table:

WorkerId    Name                             Version
----------- -------------------------------- -----------
1           Construction                     1
1           Construction                     2
1           Demolition                       1
1           Fusion                           1
5           Fusion                           1
4           Demolition                       1
4           Demolition                       2

CertDesc table (version, primary key of the name form):

Name                             Version     Description
-------------------------------- ----------- -----------------------------------------------------------------------------------------
Construction                     1           Basic Construction -- Required for all construction workers.
Construction                     2           Full Construction -- Required for all construction managers.
Demolition                       1           Demolition -- Explosives --  Required for demolition managers.
Fusion                           1           Fusion System Control -- Includes catastrophic super-criticality recovery.
Demolition                       2           Large Scale Demolition -- Basic fission knowledge with full chemical cert.

Now. I want to get a list of all CertDesc strings, so WorkerId 1 does not have this certificate. For X = 1, I only need to get Demolition 2.

Here's almost a query that looks the best to me:

Select Distinct d.Name, d.Version, d.Description
From CertDesc d join Certs c on d.Name = c.Name and d.Version = c.Version
Where d.Name NOT IN (Select c2.Name
    From Certs c2
    Where c2.WorkerId = 1)

This query returns null rows. The problem is that Demolition strings are excluded regardless of version number. I would like to use IN with tuples:

Select Distinct d.Name, d.Version, d.Description
From CertDesc d join Certs c on d.Name = c.Name and d.Version = c.Version
Where (d.Name, d.Version) NOT IN (Select c2.Name, c2.Version)
    From Certs c2
    Where c2.WorkerId = 1)

Unfortunately, this is not valid in SQL Server. Does anyone know a good way to handle this?

Answer 1

SELECT  *
FROM    CertDesc cd
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    Certs c
        WHERE   c.WorkerId = 1
                AND c.name = cd.name
                AND c.version = cd.version
        )

, name version , :

SELECT  name, version
FROM    CertDesc
EXCEPT
SELECT  name, version
FROM    Certs
WHERE   WorkerId = 1

: SQL Server 2005.

Answer 2

- :

 select c.WorkerId, c.Name as workername, cd.version, 
       cd.name as certificatename
  from certs as c
 cross join certdesc as cd 
 where cd.version <> c.version
   and cd.name <> c.name
 order by c.workerid

:

select c.WorkerId, c.Name as workername, cd.version, 
       cd.name as certificatename
  from certs as c
 cross join certdesc as cd 
 where cd.version = c.version
   and cd.name = c.name
 order by c.workerid