title: How to order by number of rows of another table in mysql
body: <p>I have these two tables and I want to order by number of rows the second table has...I want a query to show me the <strong>postid</strong> and the <strong>count()</strong> comments of each postid...The above query that I have is working perfect but I want to add in query and the table posts just I need posts table to select * ...for another job...but it has to be on the same query... My problem is when I add and the posts table to the above query it multiplies the <strong>count()</strong> by 8 i.e. in how many fields there are in posts table... it can do his job and like this but I don't know if it's correct like that or not, or maybe there is and another way for that more simpler or correct! Thanks in advance!</p>
  
  <pre><code>       comments                          posts
  +---------+------------+       +---------+-----------------+
  |  Name   |   Type     |       |  Name   |   Type          |
  +---------+------------+       +---------+-----------------+
  | id      |int(10) prim|  +----| postid  |  int(10) prim   |
  | name    |varchar(128)|  |    | title   |  varchar(100)   |
  | email   |varchar(255)|  |    | image   |  varchar(150)   |
  | body    |   text     |  |    | video   |  varchar(200)   |
  | postid  |   int(10)  |--+    | body    |     text        |
  | dt      | timestamp  |       | author  |  varchar(50)    |
  +---------+------------+       | postdate|   timestamp     |
                                 | category|enum('1','2','3')| 
                                 +---------+-----------------+
  
  
  
      &lt;?php       
      $sql = "SELECT comments.postid, count(*) FROM comments GROUP BY comments.postid ORDER BY count(*) DESC LIMIT 3";
      $query = mysqli_query($db_conx, $sql);
      ?&gt;
  
      Output of that query:   
      +--------+----------+ 
      | postid | count(*) |      
      +--------+----------+
      | 14     | 6        |
      | 13     | 4        | 
      | 15     | 3        | 
      +--------+----------+ 
  
  
  
      &lt;?php       
      $sql = "SELECT comments.postid, count(*) FROM posts, comments GROUP BY comments.postid ORDER BY count(*) DESC LIMIT 3";
      $query = mysqli_query($db_conx, $sql);
      ?&gt;
  
      Output of that query:   
      +--------+----------+ 
      | postid | count(*) |      
      +--------+----------+
      | 14     | 48       |
      | 13     | 32       | 
      | 15     | 24       | 
      +--------+----------+ 
  </code></pre>