0 Replies Latest reply on Aug 25, 2016 2:21 AM by Jag108

    Duplicate data on select statement

    Jag108

      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.