By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,495 Members | 1,249 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,495 IT Pros & Developers. It's quick & easy.

join reducing selected data in undesired way

P: 4
Platform: SQL Server 2000 (8.00.2040, SP4, Enterprise edition)

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?
May 16 '07 #1
Share this Question
Share on Google+
3 Replies


Motoma
Expert 2.5K+
P: 3,237
The join on the actions table or the users table is causing the result set to shrink. Try doing a join on actions and request to see if you get the right number of results.
May 17 '07 #2

scripto
100+
P: 143
try this

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
left outer 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
May 17 '07 #3

P: 4
Left join worked, thanks.
May 17 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.