title: Query two tables with conditional order by in MYSQL
body: <p>There are two example tables which look like</p>
  
  <h2>tasks</h2>
  
  <pre>
   id | title  | user_id |
  ----|--------|---------|
   1  | test1  |    1    |
   2  | test2  |    1    |
   3  | test3  |    1    |
   4  | test4  |    1    |
   5  | test5  |    1    |
   6  | test6  |    1    |
   7  | test7  |    1    |
   8  | test8  |    1    |
   9  | test9  |    1    |
   10 | test10 |    2    |
  </pre>
  
  <h2>responses</h2>
  
  <pre>
   id  | task_id  |  status   |
  -----|----------|-----------|
   1   |    2     |  pending  |
   2   |    2     |  pending  |
   3   |    2     |  accepted |
   4   |    2     |  accepted |
   5   |    2     |  declined |
   6   |    2     |  declined |
   7   |    3     |  pending  |
   8   |    4     |  declined |
   9   |    5     |  accepted |
   10  |    6     |  pending  |
   11  |    6     |  accepted |
   12  |    7     |  pending  |
   13  |    7     |  declined |
   11  |    8     |  accepted |
   12  |    8     |  declined |
  </pre>
  
  <ul>
  <li>'task_id' in responses is foreign key reference <code>tasks</code>.id</li>
  </ul>
  
  <p>If I want to show a table which contains all tasks of user_id=1, the columns are:</p>
  
  <ul>
  <li>task id</li>
  <li>task title</li>
  <li>number of how many pending responses</li>
  <li>number of how many accepted responses</li>
  <li>number of how many declined responses
  additionally, each column must be sortable and task id should be distinct</li>
  </ul>
  
  <p>In the example, the result should be:</p>
  
  <pre>
   task_id | title | pending | accepted | declined 
  ---------|-------|---------|----------|----------
      1    | test1 |    0    |    0     |    0
      2    | test2 |    2    |    2     |    2
      3    | test3 |    1    |    0     |    0    
      4    | test4 |    0    |    0     |    1
      5    | test5 |    0    |    1     |    0
      6    | test6 |    1    |    1     |    0
      7    | test7 |    1    |    0     |    1
      8    | test8 |    0    |    1     |    0
      9    | test8 |    0    |    0     |    0
  </pre>
  
  <ol>
  <li><p>How to do the query once to get such a query result ? (If possible)</p></li>
  <li><p>I tried to do the query like this:</p></li>
  </ol>
  
  <pre>
      select `tasks`.*, count(`task_id`) as number
      from `tasks`
      left join `responses` on `responses`.`task_id`=`tasks`.`id`
      where `tasks`.`user_id`=1 AND (`responses`.`status`='pending' or `responses`.`task_id` is NULL)
      group by `tasks`.`id`
      order by `number`
      limit 0,50
  </pre>
  
  <p>However, this only works for 'pending' and no response. Meanwhile, there is error in this query even only for 'pending', those tasks with 0 pending response but have accepted or declined responses are missing through this query. How to correct it to get full tasks list?</p>
  
  <p>Thank you for your time!</p>