Hi Tim - I'm something of a SQL perfectionist, so these recommendations come from that perspective.
Although your query would
probably return what you want, I'd suggest the below query instead for the following reasons:
- Distinct should not be necessary, as there should only be one record per EMPLOYEE, and the joins will restrict the other two queries to one record (max) each
- The HROrganization should always be included in SQL joins and the WHERE statement because it is a key field in almost every table. If you don't include it, the query will be slower and you may get Infor sample records
- I ALWAYS exclude deleted records from every table - Landmark doesn't actually "delete" anything, it just marks the records as deleted, so if you don't exclude the records, they may appear even though you can't see them in Landmark. (Note that the query below uses the DB2 syntax, so yours may be different)
- you may want to only include active address records, but that depends on your data-entry methods (do addresses get marked as inactive, or are they deleted?)
Anyway, here is the query I would use:
SELECT
e.EMPLOYEE
,e.NGIVENNAME
,e.NFAMILYNAME
,ra.PADAADDRESSLINE1 AS ResAddr1
,ra.PADAADDRESSLINE2 AS ResAddr2
,ra.PAMUNICIPALITY AS ResCity
,ra.PASTATEPROVINCE
,ra.PAPOSTALCODE AS ResZip
,ma.PADAADDRESSLINE1 AS MailAddr1
,ma.PADAADDRESSLINE2 AS MailAddr2
,ma.PAMUNICIPALITY AS MailCity
,ra.PASTATEPROVINCE AS MailState
,ma.PAPOSTALCODE AS MailZip
FROM
LANDMARK.LTMTRAIN.EMPLOYEE e
LEFT OUTER JOIN LANDMARK.LTMTRAIN.EMPLOYEEADDRESS ra
ON e.HRORGANIZATION=ra.HRORGANIZATION
AND e.EMPLOYEE=ra.EMPLOYEE
AND e.USEFORPAYROLL = ra.EMPLOYEEADDRESS
AND ra.ACTIVE = 1 -- only use this if your data entry folks are correctly marking the records as "Active"
AND HEX(ra.DELETEFLAG)=REPEAT('0',32) -- this excludes any deleted records
LEFT OUTER JOIN LANDMARK.LTMTRAIN.EMPLOYEEADDRESS ma
ON e.HRORGANIZATION=MA.HRORGANIZATION
ON e.EMPLOYEE=ma.EMPLOYEE
AND e.USEFORMAILING = ma.EMPLOYEEADDRESS
AND ma.ACTIVE = 1 -- only use this if your data entry folks are correctly marking the records as "Active"
AND HEX(ma.DELETEFLAG)=REPEAT('0',32) -- this excludes any deleted records
WHERE
e.HRORGANZATION = {whatever}
and HEX(e.DELETEFLAG)=REPEAT('0',32)