SELECT t1.orgid, AO_MEM.USER_KEY, AOO.NAME, c.ID, c.user_name, c.first_name, c.last_name, c.email_address, t1.project, t1.proj_key
FROM AO_54307E_ORGANIZATION AS AOO
INNER JOIN AO_54307E_ORGANIZATION_MEMBER AS AO_MEM ON AOO.ID=AO_MEM.ORGANIZATION_ID
INNER JOIN cwd_user AS c ON AO_MEM.USER_KEY=CONCAT("JIRAUSER",c.ID)
INNER JOIN (
    SELECT AOP.ORGANIZATION_ID AS orgid, p.pname AS project, p.pkey AS proj_key FROM AO_54307E_ORGANIZATION_PROJECT AS AOP
    INNER JOIN project AS p ON AOP.PROJECT_ID=p.ID
) t1
ON AO_MEM.ORGANIZATION_ID=t1.orgid;