troubleshooting Question

How can one use a SELECT statement to make a list for IN syntax to look through?

Avatar of madrum
madrumFlag for United States of America asked on
Oracle Database
6 Comments2 Solutions254 ViewsLast Modified:
I am trying to determine which users have not completed the reading of a notice.  I have a table logging folk who have read a pop-up notice in a table called NOTICES_LOG.  I have a table where all the users info resides call SECURITY.  Here is what I think should work:

SELECT   userid
FROM      security
WHERE   agencyid = 'DCFS'
AND        userid NOT IN  
                (SELECT   n.userid
                 FROM     security s,
                                notices_log n,
                                locations l
                 WHERE   l.location_id = s.location_id      
                 AND    s.userid = n.userid
                 AND    n.notice_id = '4'
                 AND    s.agencyid = 'DCFS'

The sub query gives me a list of folk who have read the notice.  I thought I should be able to use this select statement for the list the 'NOT IN' syntax would parse through.  

I haven't been able to find any examples for this kind of sub query.  I thought at one point that  maybe I needed to format the output of the sub query so the results from the select statement would render like ('3453', '234', '543'), but I did not have any luck finding anything that would do that.  I am certain that this should be able to be done in a select statement.  Can anyone point me in the right direction?

Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros