6 Replies Latest reply on Oct 7, 2013 1:51 PM by DanielShanahan

    Execute SQL Join Statement from multiple child tables

    DanielShanahan

      I'm a bit stuck on an eSQL statement. In my WHERE clause I would like to include conditions from two different child tables. The data structure is as follows:

       

      ADDRESS --< JOB --< JOBITEM

      ADDRESS --< JOB --< PAYMENT

       

      or linearly, without repetition:

       

      ADDRESS¬

      JOB¬

      JOBITEM | PAYMENT

       

      or in a narrative:

       

      One address can have many jobs and one job can have many job items as well as many payments.

       

      Here is what I have (which works):

      ExecuteSQL ( "

      SELECT a.salesTaxRef, SUM ( ji.rate )

      FROM address a

      JOIN job j

      ON a.ID = j.addressID

      JOIN jobItem ji

      ON j.ID = ji.jobID

      WHERE a.state='OH'

      AND ji.item LIKE 'Sweep %'

      GROUP BY a.salesTaxRef

      ORDER BY a.salesTaxRef

      " ;

      "…" ; ""

      ) // end SQL

       

      I'd like the WHERE clause to be this:

       

      WHERE a.state='OH'

      AND ji.item LIKE 'Sweep %'

      AND p.dateRecd >=?

      AND p.dateRecd <=?

       

      But I'm not sure how to include the payment table (payment p) in the JOIN ON statement. Of course, I could do this:

       

      FROM address a

      JOIN job j

      ON a.ID = j.addressID

      JOIN payment p

      ON j.ID = p.jobID

       

      But then I'd be in the same boat as before, trying to get the Job Items in the SQL.

       

       

      Hope that all makes sense. Any suggestions?