Help with WHERE clause

try

SELECT a.REQ_CDE 
FROM REQUIREMENTS a
inner join 
(
select 'APPL' as req
union
select'GOALS'
union
select'CTRN1'
union
select 'CTRN2'
union
select 'CTRN3') b on a.req_cde = b.req
where a.COMPLETION_STS = 'C'

Select allOpen in new window

although your existing query will return all req_cde that has completion_sts = 'c'

so I'm not sure I'm understanding your question. Can you please post sample data and expected resutls?

or maybe you're looking for this

SELECT a.REQ_CDE 
FROM REQUIREMENTS a
WHERE a.req_cde = ALL (
select 'APPL' as req
union
select'GOALS'
union
select'CTRN1'
union
select 'CTRN2'
union
select 'CTRN3') and a.COMPLETION_STS = 'C'

Select allOpen in new window

Let me show you the whole statement I'm having an issue with and the result which is not correct..

CASE WHEN cdy.stage in ('UACEP', 'UDEPS','UREG', 'TACEP', 'TREG', 'TDEPS') THEN 'YES'
      WHEN cdy.stage in ('TAPP','TAPPN') AND EXISTS (SELECT REQ_CDE FROM REQUIREMENTS WHERE (REQ_CDE IN ('APPL','GOALS','CTRN1','CTRN2','CTRN3') AND
      COMPLETION_STS = 'C')) THEN 'YES'
ELSE 'NO' END

So in my CASE statement when they are in these stages ('UACEP', 'UDEPS','UREG', 'TACEP', 'TREG', 'TDEPS') return a 'Yes'.

When the stage is in ('TAPP','TAPPN')  and a row in the requirements table exists containing these requirements ('APPL','GOALS','CTRN1','CTRN2','CTRN3') and all of these existing requirements are set to 'C' then return a 'YES'

I have a record where the following exist PHST,GOALS,APPL,CTRN1, but only 3/4 have 'C', it should return 'NO'.  Hope that makes sense!  Thanks for help!

I just figured it out, there is actually another column that indicates if everthing is complete, I just missed it.  Thanks so much for the assistance.