title: Two IDs matching one causing duplicates
body: <p>I am trying to inner join however I keep getting this duplicate pop up where there are two job IDs matching one Invoice ID (inner joined with a middle table that links both).</p>
  <p>I want to only get 1 invoice id and summing the total despite 2 job ids matching it.</p>
  <p>Basically there is AINVOICEID (table:Invoice ) matching ABINVOICEID (table:INLines) and inside the INLines table, it contains ARJOBID that matches the JOBID in Jobs.</p>
  <pre><code>Select distinct sum(totalBASE) as InvoiceTotal,
         DATEADD(MONTH, DATEDIFF(MONTH, 0, InvDate), 0)
  from (select distinct left(JOBID,5)as JOBID 
        from jobs 
        group by JOBID
        ) jobs
  inner join (select distinct ABINVOICEID, left(ARLJOBID,5) as arljobid 
              from INLines
              group by ARLJOBID, ABINVOICEID
              )  INLines
  on left(ARLJOBID,5) = left(JOBID,5)
  inner join (select distinct AINVOICEID
                     , sum(totalBASE) as totalBASE
                     , InvDate 
                 from Invoice 
                 group by AINVOICEID, InvDate
              )  Invoice 
  on AINVOICEID = ABINVOICEID
  where left(JOBID ,5)=left(ARLJOBID,5) and AINVOICEID = ABINVOICEID 
  and InvDate between '05/01/2022' AND '05/31/2022'
  group by left(JOBID ,5), DATEADD(MONTH, DATEDIFF(MONTH, 0, InvDate), 0)
  </code></pre>