SQL - Verwendung von IN über Tabellen, die über einen mehrzeiligen Schlüssel verbunden sind

Question

Also gut. Ich habe einen kleinen Satz von Beispieltischen vorbereitet. Ich werde nur das Beispiel geben, weil es der beste Weg ist, wie ich das Problem kommunizieren kann.

Tabelle Certs:

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 Tabelle (Version, Name als Primärschlüssel):

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.

Jetzt. Ich möchte eine Liste aller CertDesc-Zeilen erhalten, bei denen WorkerId 1 dieses Zertifikat NICHT besitzt. Für X = 1 sollte ich nur Demolition 2 erhalten.

Hier ist die fast fertig gestellte Abfrage, die mir am besten zu gefallen scheint:

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)

Diese Abfrage gibt null Zeilen zurück. Das Problem ist, dass die Demolition-Zeilen unabhängig von der Versionsnummer ausgeschlossen werden. Ich würde gerne IN mit Tupeln verwenden:

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)

Leider ist dies in SQL Server ungültig. Kennt jemand einen guten Weg, um dies zu erreichen?

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
        )

, oder, falls name y version sind genug, nur dies:

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

Edit: Diese letzte Abfrage funktioniert nur mit SQL-Server 2005.

Answer 2

Könnte so etwas funktionieren?

 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

Zum Vergleich:

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