Some employees at my company want an internal tool with a way to approve new purchases before we expense them to the proposed departments. So someone will submit a purchase order for approval, and then anywhere from 1-5 people would have to approve it, depending on the budget accounts involved.
I have a Postgres DB. The users table contains the 5 employees who need to approve orders, the approvals table contains the purchase orders, and the user_approvals table contains which users need to be approve a given proposal.
Let's say that the approval with an id of 1 needs to be approved by employees with ids 3 and 4. The user_approvals table would have two entries--(approval_id: 1, user_id: 3) and (approval_id: 1, user_id: 4).
When user 3 looks at the web page that contains the purchase orders they need to review, I pull the relevant POs:
SELECT * FROM approvals
WHERE id IN (
SELECT approval_id FROM user_approvals
WHERE user_id = 3)
That works. It grabs the relevant info from the DB entry. It also ensures that no employee sees any purchase orders that don't require their approval.
However, I would like for user 3 to ALSO be able to see if user 4 has approved the purchase order yet.
I want each user to see the purchase orders that involve them, as well as the responses from anyone else that's involved. I don't mind making two separate queries if needed.
Should I follow up with something like the following?
SELECT status FROM user_approvals
WHERE approval_id IN (
SELECT id FROM approvals
WHERE id IN (
SELECT approval_id FROM user_approvals
WHERE user_id = 3))
This seems to work in this case, but also seems redundant.
