AnsweredAssumed Answered

Query Issue

Question asked by eabollinger on Jul 13, 2012
Latest reply on Jul 19, 2012 by Alex_Feldstein
My query skills seem to have lost me today...very simply I am trying to get the following:
Resource ID from srm_resources
Resource Last Name from srm_resources
Resource Primary Role ID from prj_resources
Resource Primary Role Name from srm_resources for the Primary Role ID value - using the last name value

I have tried several variations of the below query, starting simple and adding in the sub-select query (which obviously will return all the user 'roles'. However the problem is obviously returning the single unique roll last name to match to a resource.
This is just one component of a much larger working query where I need to now get the Primary Role id and name for each resource in the portlet.

example below is just one of the iterations of my tries.

select
q.Last_Name as ResLastName
, q.resid as ResourceID
, q.last_name as roleName
, q.RoleID as RoleID
from niku.srm_resources A join niku.srm_resources B on a.id = B.Id join NIKU.prj_resources c on a.id = c.prprimaryroleid,
(select resid, last_name, RoleID from (SELECT p.prid resourceid,
p.PRUID UNIQUE_CODE,
r.last_name last_name,
r.id resid,
p.prprimaryroleid RoleID
FROM niku.srm_resources r join niku.prj_resources p on r.id=p.prid
AND p.prisrole = 1)) q
where c.prprimaryroleid = b.id;

Any query advice is appreciated.
AB

Outcomes