CODEWITHSUNDEEP
sqlsql-server
jtpereyda
jtpereyda

Reputation: 7385

SQL -- Using IN over tables joined on multiple-row key

Alright. I've got a small set of sample tables set up. I'll just give the example because it's the best way I can communicate the issue.

Certs 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, Name form Primary Key):

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 rows such that WorkerId 1 does NOT have that cert. For X = 1, I should only get Demolition 2.

Here's the almost-there 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 zero rows. The problem is that Demolition rows get excluded regardless of the version number. What I would like is 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 invalid in SQL Server. Does anybody know a good way to get at this?

Upvotes: 3

Views: 190

Answers (2)

Quassnoi
Quassnoi

Reputation: 425613

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
        )

, or, if name and version are enough, just this:

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

Edit: This latter query only works with SQL-Server 2005.

Upvotes: 5

Shan Plourde
Shan Plourde

Reputation: 8726

Would something like this work: 

 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

To compare: 

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

Upvotes: 0

Related Questions