title: Compound query solutions based on no.of rows
body: <p>I have three table</p>
  
  <p>td_idea</p>
  
  <pre><code>|------------|-------------|
  | idea_id    | idea_name   |
  |------------|-------------|
  </code></pre>
  
  <p>then td_idea_comment</p>
  
  <pre><code>|-----------|------------|----------
  |comm_id    | idea_id    | user_id  |
  |-----------|------------|----------|
  </code></pre>
  
  <p>and td_idea_like</p>
  
  <pre><code>|-----------|------------|----------
  |comm_id    | idea_id    | user_id  |
  |-----------|------------|----------|
  </code></pre>
  
  <p>Now I need to use a query which will order the idea in ascending order based on the no. of votes in td_idea_like and no. of comments on td_idea_comment.</p>
  
  <p>A example to the three tables is as follows</p>
  
  <p>td_idea</p>
  
  <pre><code>|------------|-------------|
  | idea_id    | idea_name   |
  |------------|-------------|
  | 1          | Pink Ruby   |
  |------------|-------------|
  | 2          | Black_ruby  |
  |------------|-------------|
  </code></pre>
  
  <p>td_idea_comment</p>
  
  <pre><code>|------------|-------------|---------------|
  | comm_id    | idea_id     | user_id       |
  |------------|-------------|---------------|
  | 1          |     1       |     1         |
  |------------|-------------|---------------|
  | 2          |     2       |     1         |
  |------------|-------------|---------------|
  | 3          |     1       |     2         |
  |------------|-------------|---------------|
  | 4          |     1       |     3         |
  |------------|-------------|---------------|
  </code></pre>
  
  <p>td_idea_like</p>
  
  <pre><code>|------------|-------------|---------------|
  | like_id    | idea_id     | user_id       |
  |------------|-------------|---------------|
  | 1          |     1       |     1         |
  |------------|-------------|---------------|
  | 2          |     2       |     1         |
  |------------|-------------|---------------|
  | 3          |     1       |     2         |
  |------------|-------------|---------------|
  | 4          |     1       |     3         |
  |------------|-------------|---------------|
  </code></pre>
  
  <p>I used this query</p>
  
  <pre><code>SELECT * FROM td_idea,td_idea_comment,tyd_idea_like
  
  WHERE td_idea.idea_id=td_idea_comment.idea_id
  AND td_idea.idea_id=td_idea_like.idea_id
  
  Order BY (SELECT COUNT(*) AS tot_comment FROM td_idea,td_idea_comment
  WHERE td_idea.idea_id=td_idea_comment.idea_id),
  (SELECT COUNT(*) AS tot_like FROM td_idea,td_idea_like
  WHERE td_idea.idea_id=td_idea_like.idea_id)
  </code></pre>
  
  <p>but it returns me zero if in any case either there is no row in td_idea_comment based on a particular idea or no row in td_idea_like based on a particular idea.</p>
  
  <p>Say:</p>
  
  <ul>
  <li>idea id 1 has 3 likes 3 comments</li>
  <li>idea id 2 has 1 likes 2 comments</li>
  <li>idea id 3 has 0 likes 4 comments</li>
  </ul>
  
  <p>then for idea id 3, the resultant is returned as 0.</p>
  
  <p>While I want it to be sorted as this</p>
  
  <pre><code>idea id 1(since 3+3)
  idea id 3(since 0+4)
  idea id 2(since 1+2)
  </code></pre>