CODEWITHSUNDEEP
mysqlsql
SnowmanXL
SnowmanXL

Reputation: 844

Exclude based on sub-table's value

Consider the table Audit, and AuditStatus. Where auditId in AuditStatus is a foreign key, mapping the pk of table Audit.

table Audit
id    | auditName   |
1     | test        |
2     | fooTest     |
3     | barTest     |

table AuditStatus
id  | auditId | status    | 
11  | 1       | started   |
12  | 1       | completed |
13  | 2       | started   |

How can I only select the entries of table Audit, which do not have a AuditStatus.status 'completed'.

The result in this case would be:

2     | fooTest     |   
3     | barTest     |

I have updated the question and the result example, to make it more clear. The relation Audit -> AuditStatus is a one to many. And I want to exclude the Audits which have a refrerence to an AuditStatus with status 'complete'

Upvotes: 1

Views: 50

Answers (2)

FanoFN
FanoFN

Reputation: 7114

You should post your attempted query into your question, not as comment. Anyway, your query is actually correct but your condition is incorrect. Let's inspect your query:

SELECT * 
  FROM Audit a 
  WHERE NOT EXISTS (
      SELECT s.auditId
        FROM AuditStatus s
       WHERE a.id = s.auditId AND s.status != 'completed'
);

You're suppose to find where the status is not complete, which is true in the subquery but the problem here is you're doing a NOT EXISTS which negates the correct result you're getting from the subquery.

This is what your subquery will return:

id auditId status
11 1 started
13 2 started

Then when your NOT EXIST negates the auditId being returned, you'll get this result instead:

id auditName
3 barTest

Which is correct according to the condition; auditId=3 wasn't returned in the subquery. What you need to modify is actually very simple, you just need to make the subquery return status = completed as true then NOT EXISTS will return any Audit.Id that doesn't match with the correlated subquery. Therefore:

SELECT * 
  FROM Audit a 
  WHERE NOT EXISTS (
      SELECT s.auditId
        FROM AuditStatus s
       WHERE a.id = s.auditId AND s.status = 'completed'
);

And that's it, you should be getting the result you looking for.

Demo fiddle

Upvotes: 1

DhruvJoshi
DhruvJoshi

Reputation: 17126

maybe use a left join like below which only joins on Audit Status on Fk as well as status constraint

SELECT * 
FROM Audit A
LEFT JOIN AuditStatus ATS
ON A.id= ATS.auditId AND ATS.Status ='completed'
WHERE AS.auditId IS NULL

Upvotes: 1

Related Questions