Link to home
Start Free TrialLog in
Avatar of madrum
madrumFlag 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
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of 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
Avatar of madrum

ASKER

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

Thanks again,
:)mary
- great madrum and thanks for coming back. the clarification would help others in the future :)
Avatar of slightwv (䄆 Netminder)
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.