AnsweredAssumed Answered

SQL query assistance to only retrieve projects meeting specific condition

Question asked by vtleogal2 on Dec 29, 2015
Latest reply on Dec 29, 2015 by vtleogal2

Hi, I 've tried as many different SQL commands as I can and I cannot get the query to show me what I want.

 

14.2 SQL 2012 (on premise )

 

We have a custom attribute that holds a numeric number from a custom view. This number can be associated to 1 or many Clarity project ids.

I only want the query to retrieve the custom attribute number if ALL associated Clarity projects are inactive.

 


Example:

 

Custom attribute (xproject)  = 12345

This value of 12345 is associated to the following Clarity projects:

project A - active

Project B- inactive

Project C - inactive

 

 

Custom attribute (xproject) = 55555

This one is associated to the following Clarity projects:

Project  D = inactive

Project E = inactive

Project F = inactive

 

In my query, I would only want the results to show xproject 5555 (and nice if it would show the associated Clarity project id), since ALL of its associated clarity projects are inactive.

 

I have tried Where exists, the all command, group by w/ having and I cannot get it to ONLY show me the above. It will show me ALL projects or only those that are inactive.

This is the very basic query that I have been trying to manipulate with various commands.  This will be part of a much larger query, but the larger part is all working, I just need to see if this requirement can be met.

 

select o.xproject, p.unique_name from odf_ca_project o

inner join srm_projects p on o.id= p.id

where p.IS_ACTIVE ='0'

 

this above would only show the inactive clarity projects, but it would return xproject 12345 AND 55555, where I just want it to show 55555. How do I manipulate the where clause or let me know if I need to include more information?

 

thanks!

Outcomes