Oracle query – How to check if a user is the only one assigned to a project

I am trying to determine if a user is assigned to any project with a specific role (ProjMemberTypeID) and if so, if s/he is the only one.

There is “projMember” table with Project ID, User ID and Project Member Type ID. I need to know if there are any project IDs where this user is the only one with member type id of 1.

Is it also possible to get some project info (like name, …) joining the requested query with “Project” table, based on Project ID?

I tried this but it only gives me how many users are assigned to projects and if I filter by user ID I get how many projects user is assigned to but don’t know how to check if s/he is the only one.

select count(userid), projectid 
from projmember 
where projmembertypeid = 1 -- and userid=73
group by projectid
order by projectid;

Answer

You can use a having clause:

select projectid 
from projmember 
where projmembertypeid = 1
group by projectid
having min(userid) = 73 and max(userid) = min(userid)
order by projectid;

The query filters on the relevant member type, then aggregates by project; all that is left to do is ensure that the given user was found, and no other.

Advertisement

Answer