CODEWITHSUNDEEP
phpjoin
Dion
Dion

Reputation: 203

How to query 2 tables

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)?

Upvotes: 0

Views: 62

Answers (2)

Zohaib
Zohaib

Reputation: 328

I hope this query will fetch your desire data. Check and let me know if it works for you.

   SELECT SUM(tb1.`InMonthActual`) 
   FROM `tblgl` as tb1 
   JOIN `tblcostcentrehierarchy` as tb2 ON tb1.`CostCetntreCode` = tb2.`CostCentreCode`
   JOIN `tblcostcentreallocations` as tb3 ON tb1.`CostCetntreCode` = tb3.`CostCentreCode`
   WHERE tb1.`PeriodNumber` = '2' AND tb2.`Level17` = "RWK312 CORPORATE" AND tb3.`Username` = "jonest"

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64496

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

Upvotes: 0

Related Questions