I am trying to write a query for a report that has 4 joined tables, all is fine until I add one particular table then I get 110 records returned instead of 11
There is a many to many relationship between the LABTRANS table and the WORKLOG table.
SELECT P.DISPLAYNAME || ' (' || LT.LABORCODE || ')' AS "Labor"
,LT.CRAFT AS "Craft"
,LT.REFWO AS "Work Order"
,LT.STARTDATE AS "Start Date"
,LT.REGULARHRS AS "Regular Hours"
,LT.PAYRATE AS "Rate"
,WO.WORKTYPE AS "Work Type"
,WL.DESCRIPTION AS "Description"
FROM LABTRANS LT
JOIN WORKORDER WO ON LT.REFWO = WO.WONUM
JOIN WORKLOG WL ON WL.RECORDKEY = WO.WONUM AND WL.RECORDKEY = LT.REFWO -- Offending join
JOIN PERSON P ON LT.LABORCODE = P.PERSONID
WHERE LT.STARTDATE BETWEEN TO_DATE('01/01/2016', 'dd/mm/yyyy') AND TRUNC(SYSDATE)
AND LT.CRAFT NOT IN ('DTZ','BMS')
I get the results I need if I use the EXISTS statement, but then I am not able to display the work log description data.