title: Get data from multiple inner joins
body: <p>I have following tables.</p>
  
  <h2>Table: <strong>PollingBooth</strong>  PBId is primary key.</h2>
  
  <pre><code>PBID  | PBName |pdivID      
  -----------------------
  1     | Jaya PB | 1
  
  2     | SP PB   | 1
  </code></pre>
  
  <p>Table: PollingDivision - pdID is primary key.</p>
  
  <pre><code>----------------
  
  pdID  |pdName
  ------------------
  1     | Homagama 
  
  2     | Maharagama  
  </code></pre>
  
  <p>Table:ElectionOfficial -EoId is primary key.</p>
  
  <pre><code>----------------
  EoID   |EoName
  ---------------
  1      | Sam
  
  2      | Ram
  
  3      | Feston
  </code></pre>
  
  <p>Table: Election-  elecID is primary key.</p>
  
  <pre><code>--------------------------------------
  elecID    | ElecName
  --------------------------------------
  1         | Presidential Election 2013
  
  2         | General Election 2014
  </code></pre>
  
  <p>Table: PollingBoothElection-  Both pbID and elecID columns forms a primary key</p>
  
  <pre><code>--------------------------------
  pbID   | elecID |numofEOs
  --------------------------------
  1      | 1      | 2
  
  2      | 1      | 3
  </code></pre>
  
  <p>Table: PollingBoothElectionOfficial. All three columns together a combined primary key.</p>
  
  <pre><code>----------------------------------
  
  pbID   | elecID   | eOfficialID 
  ----------------------------------
  
  1      | 1        |1
  
  1      | 1        |2
  </code></pre>
  
  <p>I want to select all the PollingBooth names of a particular PollingDivision in which count of the eoID for each PollingBooth for a particular election is less than the allowed number of election officials for a particular election.</p>
  
  <p>In other words I want to select all the PollingBooth Names in which count of all eOfficialID in PollingBoothElectionofficial table for a given pbID and an elecID is less than the value of numberofEOs of PollingBoothElection for the same eleID and the pbID and I want to do this for all the polling booths of a particular PollingDivision.  </p>
  
  <p>Here's what I tried.</p>
  
  <pre><code>   SELECT PB.PBName, COUNT(PBEO.eOfficialID)
     FROM PollingBoothElection PBE
     INNER JOIN PollingBooth PB ON PBE.pbID=PB.PBID
     INNER JOIN PollingDivision PD ON PB.pdivID=PD.pdID
     INNER JOIN PollingBoothElectionOfficial PBEO ON PBE.elecID=PBEO.elecID 
     AND PBE.pbID=PBEO.pboothID
     WHERE PBE.elecID=1 AND PD.pdName='Homagama' AND PBE.numOfEO&gt;(SELECT COUNT(PBEO.eOfficialID))
     GROUP BY PB.PBName; 
  </code></pre>
  
  <p>But this says that i can't include aggregate function in where clause. So how can I check count of each polling booth's election officials for a particular election in PollingBoothElectionOfficial table is not greater than the number of allowed election officials given in the PollingBoothElection table.</p>
  
  <p>Is there a way that I can achieve this with IF ELSE Statements?</p>
  
  <p>Any help would be great.
  Thanks in advance.</p>