AnsweredAssumed Answered

Execute SQL Join Statement from multiple child tables

Question asked by DanielShanahan on Oct 4, 2013
Latest reply on Oct 7, 2013 by 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?

Outcomes