title: getting wrong values after joining tables - MySQL - PHP
body: <p>I have to generate a report using 4 tables, I tried a lot of joins, but I get wrong values :(</p>
  
  <p>Hope someone will help me.</p>
  
  <p>StaffAssign Table:</p>
  
  <pre><code>----------------------------
  | Tid | StaffId | Planned  |
  ----------------------------
  | 123 |   2     | 10:00:00 |
  | 123 |   4     | 05:00:00 |
  | 124 |   2     | 09:00:00 |
  ----------------------------
  </code></pre>
  
  <p>ActualEffort Table:</p>
  
  <pre><code>--------------------------------------------
  | Tid | StaffId | ActualEffort  | logdate  |
  --------------------------------------------
  | 123 |   2     | 05:00:00      |2012-09-01|
  | 123 |   4     | 05:00:00      |2012-09-01|
  | 123 |   2     | 06:00:00      |2012-09-03|
  | 124 |   2     | 09:00:00      |2012-09-04|
  --------------------------------------------
  </code></pre>
  
  <p>ProjectList Table:</p>
  
  <pre><code>-------------------
  | ProjectId | Tid |
  -------------------
  |    1      | 123 |
  |    2      | 124 |
  -------------------
  </code></pre>
  
  <p>ProjectName Table:</p>
  
  <pre><code>-------------------
  | Id  | name      |
  -------------------
  | 1   | project1  |
  | 2   | project2  |
  -------------------
  </code></pre>
  
  <p>Below is the Query I'm using:</p>
  
  <pre><code>SELECT P.id AS projectid,P.name,
  SEC_TO_TIME(SUM(TIME_TO_SEC(A.planned)))planned,SEC_TO_TIME(SUM(TIME_TO_SEC(E.actualeffort)))actual FROM actualeffort E 
  INNER JOIN staffassign A ON A.tid = E.tid
  INNER JOIN projectlist L ON L.tid = A.tid
  INNER JOIN projectname P ON P.id = L.projectid 
  WHERE E.logdate BETWEEN FROM_UNIXTIME('1346475600') AND FROM_UNIXTIME('1348290000')
  GROUP BY P.id
  </code></pre>
  
  <p>I'm getting this:</p>
  
  <pre><code>---------------------------------------------
  | projectid | name     | planned | actual   |
  ---------------------------------------------
  |     1     | project1 | 15:00:00| 12:00:00 |
  |     2     | project2 | 09:00:00| 09:00:00 |
  ---------------------------------------------
  </code></pre>
  
  <p>But it should be like:</p>
  
  <pre><code>---------------------------------------------
  | projectid | name     | planned | actual   |
  ---------------------------------------------
  |     1     | project1 | 15:00:00| 16:00:00 |
  |     2     | project2 | 09:00:00| 09:00:00 |
  ---------------------------------------------
  </code></pre>
  
  <p>I'm very confused that, I don't know where I'm going wrong with the joins.</p>
  
  <p>Someone please help me, I'm struck with this.</p>