如何查询2个表

Question

I need to sum the transactions in tblgl (tblgl.SUM(InMonthActual)) for a selection of cost centres (tblgl.CostCentreCode) where the following conditions are met:

1. tblgl.PeriodNumber = 2
2. tblgl.CostCentreCode = tblcostcentrehierarchy.CostCentreCode WHERE tblcostcentrehierarchy.Level7 = "RWK312 CORPORATE"
3. tblgl.CostCentreCode = tblcostcentreallocations.CostCentreCode WHERE tblcostcentreallocations.Username = "jonest"

At the moment I'm running 3 separate queries to create an array which is used in the next query.

Is there a way to do it in one (maybe using JOIN)?

Answer 1

Give it a try

SELECT SUM(tblgl.InMonthActual) FROM tblgl
INNER JOIN tblcostcentrehierarchy ON (tblgl.CostCentreCode = tblcostcentrehierarchy.CostCentreCode AND tblgl.PeriodNumber = 2)
INNER JOIN tblcostcentreallocations ON  (tblgl.CostCentreCode = tblcostcentreallocations.CostCentreCode)
WHERE tblcostcentreallocations.Username = "jonest" AND tblcostcentrehierarchy.Level7 = "RWK312 CORPORATE"
GROUP BY tblgl.InMonthActual

Hope it works fine