madrum
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Just as a point of clarification, everyone one of the examples above works.
Thanks again,
:)mary
Thanks again,
:)mary
- great madrum and thanks for coming back. the clarification would help others in the future :)
Glad to help.
I would take a good look at the performance between them all.
A good place to start is the Execution Plan.
I would take a good look at the performance between them all.
A good place to start is the Execution Plan.
ASKER
Regards,
:)mary