CODEWITHSUNDEEP
sql
PJW
PJW

Reputation: 5397

SQL Query to Find Matching Records but Excluding those that Match Second Criteria

I have a SQL database and I want to locate all the records that match one criteria, but exclude from that set of results the records that match a second criteria. More specifically -

Table 'Documents' with Fields = 'DocID', 'Description', 'SignID'

Table 'Signatures' with Fields = 'SignID', 'SignatoryName'

I want to find all the Documents that have been signed by a specific Signatory e.g.

SELECT d.DocID, s.SignID
FROM Documents AS d
INNER JOIN Signatures AS s ON d.SignID = s.SignID
WHERE s.SignatoryName = 'Search Name Here';

However, many documents are signed by more than one person (i.e. Documents > Signatures is One-to-Many). I want to exclude those documents which have been signed by anyone else, other than the specific Signatory in the above query. Or put another way, I want to find all those Documents that have ONLY been signed by the specific Signatory.

But I'm not sure how to revise my query to achieve this objective.

Upvotes: 0

Views: 556

Answers (2)

halfbit
halfbit

Reputation: 3464

Assuming that SignatoryName is not null, you could try:

SELECT d.DocID, s.SignID
FROM Documents AS d
WHERE 'Search Name Here' = (
  SELECT CASE WHEN MAX(SignatoryName) = MIN(SignatoryName) 
              THEN SignatoryName ELSE NULL END
  FROM Signatures AS s ON s.SignID = d.SignId
)

Upvotes: 0

Sai Avinash
Sai Avinash

Reputation: 4753

Give a try with below query:

(SELECT d.DocID, s.SignID
FROM Documents AS d)
temp1
INNER JOIN Signatures AS s ON d.SignID = s.SignID
WHERE s.SignatoryName = 'Search Name Here' and d.DOCID not in ( select DocID from temp1)

Upvotes: 1

Related Questions