Querying records with JOIN records matching a specific value (e.g., approved, not approved, pending)

user_6460057 0
Viewed 26

I have a query which returns a records and the status of various approval associated with them. I would like to only see records which have an "approved" status for ALL approvals associated with the record. The field which shows status is t3.status. If t2.status is 'approved' from ALL joined records, that is what I'm looking for.

Seems simple enough but I'm not quite sure how to write this.

SELECT *
FROM change_request
JOIN approval t2 ON t2.parentsysid = change_request.sysid  
JOIN appuser t3 ON t3.userid = t2.userId
sql
1 Answers

I don't know how the approved status is stored in your db -- I assume a column called status with value "approved". Change the query to how it is stored in your db

SELECT *
FROM change_request
JOIN approval t2 ON t2.parentsysid = change_request.sysid  
LEFT JOIN non_approve n on n.parentsysid = change_request.sysid and n.status <> 'approved'
JOIN appuser t3 ON t3.userid = t2.userId
WHERE n.parentsysid is null

How this works -- here we left join to the table to find any case of what you don't want. Then we only select cases where that join did not happen. That means there are no cases of what you don't want.

You could also use NOT EXISTS -- but on most systems that is slower then this method.

user_2157520
Related