Easy SQL

Question

Post reply

Easy SQL

Rog Saber

SSCrazy

Points: 2548
More actions
June 18, 2010 at 8:25 am

#220207

***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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jvanderberg SSCertifiable Points: 6762 More actions · Answer 2

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

Rog Saber SSCrazy Points: 2548 More actions · Answer 3

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.

jvanderberg SSCertifiable Points: 6762 More actions · Answer 4

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')

Rog Saber SSCrazy Points: 2548 More actions · Answer 5

Rog Saber

SSCrazy

Points: 2548

June 18, 2010 at 8:51 am

#1182188

That worked great. Thank you for the help.