JOIN issue

Results 1 to 2 of 2

Thread: JOIN issue

  1. #1
    Join Date
    Dec 1969

    Default JOIN issue

    Any idea why the SELECT Statement below would generate NumberJobs greater than those with an Entrydate of after &#039;2004-03-28&#039; ?<BR><BR>I probably need to change the JOINs<BR><BR><BR>SELECT Count (Jobs.JobID)As NumberJobs, Sum(Invoices.TotalExVAT) AS Income, Partners.OrgName, Partners.PartnerID, Partners.Opt <BR>FROM Jobs <BR>INNER JOIN Partners ON Jobs.PartnerID = Partners.PartnerID <BR>INNER Join Commission ON Commission.PartnerID = Partners.PartnerID <BR>INNER JOIN Invoices ON Invoices.PartnerID = Partners.PartnerID <BR>WHERE Jobs.Entrydate &#062; &#039;2004-03-28&#039; AND Invoices.Entrydate &#062;&#039;2004-03-28&#039; <BR>GROUP BY Partners.PartnerID, Partners.Orgname, Partners.Opt <BR>ORDER BY Partners.Opt

  2. #2
    Join Date
    Dec 1969

    Default RE: JOIN issue

    I&#039;m guessing your table structure/joins will give you duplicate JobId<BR><BR>JobId,PartnerId<BR>1,1<BR>1,2<BR>1,3< BR><BR>Try doing a DISTINCT Count:<BR><BR>SELECT Count (Distinct Jobs.JobID) As NumberJobs,...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts