Version : 10609

DB : PGSQL

OUTPUT :

SELECT aaauser.First_name AS "Name",
al.name "Login Name",
sduser.FIRSTNAME AS "First Name",
aaacontact.EMAILID AS "E-Mail",
deptTable.DEPTNAME AS "Department",
cisite.NAME AS "Site",
projro.rolename AS "Project Role",
aaacontact.LANDLINE AS "Phone",
aaacontact.MOBILE AS "Mobile",
sduser.JOBTITLE AS "Job title",
sduser.EMPLOYEEID AS "Employee ID",
sduser.LASTNAME AS "Last Name",
sduser.MIDDLENAME AS "Middle Name" FROM People peopleci
LEFT JOIN BaseElement baseci ON peopleci.CIID=baseci.CIID
LEFT JOIN PortalUsers portalusers ON peopleci.CIID=portalusers.CIID
LEFT JOIN CI ci ON baseci.CIID=ci.CIID
LEFT JOIN SDOrganization cisite ON ci.SITEID=cisite.ORG_ID
LEFT JOIN SDUser sduser ON portalusers.USERID=sduser.USERID
LEFT JOIN AaaUser aaauser ON sduser.USERID=aaauser.USER_ID
inner join portalusers pu on sduser.USERID=pu.USERID
inner join portaltechnicians pt on pu.ID = pt.ID
LEFT JOIN AaaUserContactInfo aaausercontact ON aaauser.USER_ID=aaausercontact.USER_ID
left join AaaLogin al on aaauser.USER_ID=al.USER_ID
LEFT JOIN AaaContactInfo aaacontact ON aaausercontact.CONTACTINFO_ID=aaacontact.CONTACTINFO_ID
LEFT JOIN UserDepartment userDept ON sduser.USERID=userDept.USERID
LEFT JOIN DepartmentDefinition deptTable ON userDept.DEPTID=deptTable.DEPTID
LEFT join sduserprojectroles sdproj on sduser.USERID=sdproj.USERID
LEFT Join projectroles projro on sdproj.projectroleid = projro.roleid