1

I am using MSSQL Server 2008. I have three tables as follows:

Table:Project Columns: ProjectID, {Primary Key} ProjectNickName, BusinessType

Table:Audit Columns: {Foreign Key}ProjectNickName, {Primary Key}AuditID, AuditCreationDate, AuditStatus

Table:Findings Columns: {Foreign Key}AuditID, FindingStatus, {Primary Key}FindingNumber

Project to Audit: One to many

Audit to Findings: One to many

I want to find all audit records with an AuditStatus of 'Open' that have NO findings with an 'Open' FindingStatus. Also the audit record must be associated with a project that has a BusinessType of 'External' and the AuditCreationDate is within the last 30 days.

Here is what I have so far:

Select a.AuditID, p.BusinessType, p.ProjectNickName, a.AuditCreationDate 
FROM Project p 
INNER JOIN Audit a ON p.ProjectNickName = a.ProjectNickName 
LEFT OUTER JOIN Findings f on a.AuditID = f.AuditID 
WHERE p.BusinessType LIKE 'External' AND AuditCreationDate >= GETDATE()-30 
      AND a.AuditStatus LIKE 'OPEN' and f.FindingStatus NOT LIKE 'OPEN'

There are two problems with the above query.

  1. There can be multiple findings for each audit, but if any one of those findings has a status of 'Open', I do not want that record to show.
  2. The above does not show disctinct audit records. If there are multiple findings for an audit that do not have an 'Open' status, it will show them. I only want distinct audit records. I looked at the following DISTINCT for only one Column, which solved that problem, but an audit record would still show if there was at least one finding that did not have an 'Open' findingStatus and another finding associated with the audit that DID have an 'Open' findingStatus. As mentioned previously, I only want audits that have no finding records with a findingStatus of 'Open' to appear in my result set.

I am guessing I may need to use some kind of nested query with a distinct function, but I can't wrap my mind around how to do it yet.

Any help is much appreciated! I tried to be as clear and precise as possible, but let me know if I can elaborate on anything.

Community
  • 1
  • 1
sgerhardt
  • 43
  • 4

4 Answers4

2
Select 
    a.AuditID, 
    p.BusinessType, 
    p.ProjectNickName, 
    a.AuditCreationDate 
FROM 
    Project p 
JOIN 
    Audit a 
    ON p.ProjectNickName = a.ProjectNickName AND
    a.AuditCreationDate >= GETDATE()-30 AND 
    a.AuditStatus ='OPEN'
LEFT JOIN 
    Findings f 
    on  a.AuditID = f.AuditID and
        f.FindingStatus ='OPEN'
WHERE 
    p.BusinessType ='External' AND      
    f.FindingStatus is null -- Here we could use any column name from the findings table. We are basically saying we don't want there to exist an associated findings entry that has the findingStatus open
ingo
  • 5,469
  • 1
  • 24
  • 19
  • Would return multiple rows for an audit with more than one closed finding – Andomar Aug 03 '11 at 02:10
  • No it would not! Because of the fact I'm joining only if the findingstatus is equal to 'OPEN' and only where that left join is null! – ingo Aug 03 '11 at 02:11
  • @ingo: Interesting, +1. The query optimizer is smart enough to see that this is equivalent of `not exists`, so I'm not sure which one would perform better – Andomar Aug 03 '11 at 02:36
1

You could use a not exists subquery to exclude audits with open findings. That will also eliminate multiple rows per audit, since you are no longer joining on the findings table.

select  a.AuditID
,       p.BusinessType
,       p.ProjectNickName
,       a.AuditCreationDate 
from    Project p 
join    Audit a 
on      p.ProjectNickName = a.ProjectNickName 
where   p.BusinessType LIKE 'External' and 
        a.AuditStatus LIKE 'OPEN' and
        a.AuditCreationDate >= getdate()-30 and 
        not exists
        (
        select  *
        from    Findings f 
        where   a.AuditID = f.AuditID and
                f.FindingStatus = 'OPEN'
        )
Andomar
  • 232,371
  • 49
  • 380
  • 404
  • This will propably perform better than my solution because here you can take advantage of indexes. My solution would probably always do a hash join and then filter so I'll upvote it. And yes Andomar my solution would work as well ..I have used it many times :) – ingo Aug 03 '11 at 02:31
  • This worked! Thank you so much to you and Ingo for your guidance :D – sgerhardt Aug 04 '11 at 02:13
0

You might be able to use a correlated subquery such as this one.

SELECT * FROM Audit A WHERE AuditID NOT IN 
(SELECT AuditID FROM Findings 
WHERE AuditID = A.AuditID and Status = 'OPEN')

Which ought to disallow any Audits that have Findings with Status = 'Open'

You'll need to complete it with your Project JOIN, et cetera.

LesterDove
  • 3,014
  • 1
  • 23
  • 24
0

You didn't specify whether it's necessary to display the row if the audit has no row in findings table at all (i.e. will return null with a left outer join). Assuming that you do want them to show, here is one way of doing it.

Select a.AuditID, p.BusinessType, p.ProjectNickName, a.AuditCreationDate 
FROM Project p 
INNER JOIN Audit a ON p.ProjectNickName = a.ProjectNickName 
WHERE p.BusinessType LIKE 'External' 
AND AuditCreationDate >= GETDATE()-30 
AND a.AuditStatus LIKE 'OPEN' 
AND a.AuditID NOT IN
(select distinct AuditID from Findings
  where FindingStatus LIKE 'OPEN')
deutschZuid
  • 1,028
  • 2
  • 15
  • 33