score:1

Proper tu use having count(distinct STATUS=1) :

create table tableA( ProjectID int, STATUS varchar2(10) );
insert all
  into tableA values(1    ,'NEW')
  into tableA values(1    ,'CHANGED')
  into tableA values(2    ,'NEW')
  into tableA values(3    ,'CHANGED')
select * from dual; 
/
select * from
(
select ProjectID, max(STATUS) STATUS
  from tableA
 group by ProjectID
having count(distinct STATUS)=1
)
where STATUS = 'NEW';

score:1

Something like this, perhaps?

SQL> with test (projectid, status) as
  2    (select 1, 'new'    from dual union     -- should be returned
  3     select 2, 'new'    from dual union
  4     select 2, 'closed' from dual union
  5     select 3, 'closed' from dual union
  6     select 4, 'new'    from dual           -- should be returned
  7    )
  8  select projectid
  9  from test
 10  group by projectid
 11  having min(status) = max(status)
 12     and min(status) = 'new';

 PROJECTID
----------
         1
         4

SQL>

score:0

I believe I have accomplished what you want, using a subquery in LINQ.

var query = (from a in context.A
                         where (from b in context.A
                                where b.ProjectID == a.ProjectID
                                select new { a.ProjectID, a.STATUS }).Distinct().Count() == 0
                                && a.STATUS == "NEW"
                         select a.ProjectID).ToList();

Essentially, the outer query just makes sure that each a record has a NEW status, and the inner query makes sure that there are no two distinct records with the given ProjectID, because if there are, one is CLOSED. I avoided using a GROUP BY since you said your database does not support LINQ's way of doing it.

I hope I understood your problem correctly, and I hope this helps!


Related Articles