Avatar of madrum
madrum
Flag for United States of America asked on

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

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?

TYIA,
:)mary
Oracle Database

Avatar of undefined
Last Comment
slightwv (䄆 Netminder)

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
OP_Zaharin

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
madrum

ASKER
I see I need to spend more time studying operators and conditions.  Thank you so much.  You have both helped me immensely.

Regards,
:)mary
madrum

ASKER
Just as a point of clarification, everyone one of the examples above works.

Thanks again,
:)mary
OP_Zaharin

- great madrum and thanks for coming back. the clarification would help others in the future :)
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
slightwv (䄆 Netminder)

Glad to help.

I would take a good look at the performance between them all.

A good place to start is the Execution Plan.