4
I've got a complex query I'm trying to build, which will select all requests that have a status_code of 1, and who's related incident has a manager_id of the specified value.
SELECT (columns desired)
FROM recipient p
JOIN requests r ON p.recipient_id=r.recipient_id
JOIN incident i ON r.request_id=i.request_id
WHERE i.manager_id='value' AND r.status_code = 1
manager_id is a clumn in incident, and status_code is a column in requests
Run this way, the query selects 6 records, which I would expect.
Complication:
I need additional data from 2 more tables. This is intended give me the user who performed the insert action on the incident, and their first and last name.
table actions (a) has:
- user_id which is FK to users table
- request_id which is FK to requests table already in query
- action_type which will need to be constrained to a value of 1 when a.request_id=r.request_id
table users (u) has columns with user name (which will replace user_id in display)
When I add these tables to the join in the following manner, my result set goes down to 1 record.
SELECT (columns desired)
FROM recipient p
JOIN requests r ON p.recipient_id=r.recipient_id
JOIN incident i ON r.request_id=i.request_id
JOIN actions a ON a.request_id=i.request_id
JOIN users u ON a.user_id=u.user_id
WHERE manager_id='value' AND status_code = 1
While I believe I need to be specific that I want the user_id from actions that performed the action_type=1, I don't believe that's what's hindering the operation (I'd have expected to get some duplicate results).
Any thoughts?
