AnsweredAssumed Answered

Query to identify projects that do not contain resources from a sub-query

Question asked by matpj on Sep 6, 2010
Latest reply on Sep 7, 2010 by matpj
Hi all,

my mind has gone totally blank, and the more I look at this problem the less luck I am having!
I have a query to identify a group of support projects in 7.5.3:
Select distinct



Project_code,



project_name









from 



niku.nbi_project_current_facts nbip,



niku.odf_ca_project cst,



niku.prtask t,



niku.prchargecode ch,



niku.prteam te,


    niku.srm_resources res






where 



cst.id = nbip.project_id


  and te.prprojectid = nbip.project_id


  and te.prresourceid = res.id



and cst.dsti_proj_act = 'SUP'



and nbip.project_class not in ('101BPM', '593TST', '501HIA', '999SUP', '570ATL')



and nbip.project_code NOT IN ('DST015588', 'DST016270')



and nbip.is_active = 1



and (cst.dsti_closed = 0 OR cst.dsti_closed IS NULL) and



t.prprojectid = nbip.project_id and



t.prchargecodeid = ch.prid



and ch.prexternalid like 'SUP%' 
I also have a query to identify a list of support resources:
select res.full_name, res.unique_name , dep.description


from niku.srm_resources res,


niku.pac_mnt_resources pac,


niku.departments dep


where res.unique_name = pac.resource_code


and pac.departcode = dep.departcode


and res.is_active = 1


and description like 'IMS%'


and UPPER(dep.description) like '%SUP%'
I need to incorporate this resource query into the first so that if there is a resource on that list that is NOT included in the project team, then the project should be returned....

Any ideas.
I'm sure it is so blatantly obvious and very easy, but I have drawn a blank and simply cant think of how to do it!! lol

thanks in advance,
Matt

Outcomes