June 18, 2010 at 8:25 am
***I should also add the I need the user details fro a user table as well in the same select.
For example:
User Table
user_id....last_name
joe..........smith
sam..........toms
bob..........johnson
ian..........taylor
tim..........adams
jim..........smith
For some reason I'm having trouble with this. I want all of the users who are Requestor ONLY. So, my result set would be ian and jim.
Contact Table
user_id....contact_type
joe..........Approver
joe..........Requestor
sam..........Approver
sam..........Requestor
bob..........Approver
bob..........Requestor
ian..........Requestor
tim..........Approver
tim..........Requestor
jim..........Requestor
June 18, 2010 at 8:32 am
the trick is to join the table against itself, so you can exclude one group:
something like this:
SELECT *
FROM SOMETABLE t1
WHERE t1.contact_type = 'Requestor'
AND t1.user_id NOT IN
(SELECT user_id
FROM SOMETABLE
WHERE user_id IS NOT NULL
AND contact_type <> 'Requestor')
you could do this with a left otuer join as well.
Rog Saber (6/18/2010)
For some reason I'm having trouble with this. I want all of the users who are Requestor ONLY. So, my result set would be ian and jim.user_id....contact_type
joe..........Approver
joe..........Requestor
sam..........Approver
sam..........Requestor
bob..........Approver
bob..........Requestor
ian..........Requestor
tim..........Approver
tim..........Requestor
jim..........Requestor
Lowell
June 18, 2010 at 8:36 am
Rog Saber (6/18/2010)
For some reason I'm having trouble with this. I want all of the users who are Requestor ONLY. So, my result set would be ian and jim.user_id....contact_type
joe..........Approver
joe..........Requestor
sam..........Approver
sam..........Requestor
bob..........Approver
bob..........Requestor
ian..........Requestor
tim..........Approver
tim..........Requestor
jim..........Requestor
SELECT user_id
FROM @table T1
WHERE
(SELECT contact_type FROM @table T2
WHERE T1.user_Id = T2.user_id
AND contact_type <> 'Requestor') IS NULL
June 18, 2010 at 8:45 am
SELECT RTOV_USERS.user_id,
RTOV_USERS.fname,
RTOV_USERS.lname
FROM RTOV_USERS, RTO_CUSTOMER_CONTACTS T1
WHERE RTOV_USERS.user_id = T1.user_id
and T1.contact_type = 'REQUESTOR'
and
(SELECT contact_type FROM RTO_CUSTOMER_CONTACTS T2
WHERE T1.user_id = T2.user_id
AND contact_type <> 'REQUESTOR') IS NULL
I get:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
June 18, 2010 at 8:48 am
Rog Saber (6/18/2010)
SELECT RTOV_USERS.user_id,RTOV_USERS.fname,
RTOV_USERS.lname
FROM RTOV_USERS, RTO_CUSTOMER_CONTACTS T1
WHERE RTOV_USERS.user_id = T1.user_id
and T1.contact_type = 'REQUESTOR'
and
(SELECT contact_type FROM RTO_CUSTOMER_CONTACTS T2
WHERE T1.user_id = T2.user_id
AND contact_type <> 'REQUESTOR') IS NULL
I get:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Change the subquery to:
(SELECT MAX(contact_type) FROM RTO_CUSTOMER_CONTACTS T2
WHERE T1.user_id = T2.user_id
AND contact_type <> 'REQUESTOR')
June 18, 2010 at 8:51 am
That worked great. Thank you for the help.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply