title: Order sql results using a Count() value from another table
body: <p>assuming we have a users table like this:</p>
  
  <pre><code>ID  |  USERNAME  |  STATUS  |
  01  |  Aname     |    1     |
  02  |  Bname     |    1     |
  03  |  Cname     |    2     |
  04  |  Dname     |    1     |
  </code></pre>
  
  <p>there are also two other tables, where, for each user, records the items that the users "has" for istance, video and photos, lets assume the video table is like this:</p>
  
  <pre><code>ID  |  USER      |  VIDEO   |
  01  |  01        |   aaaa   |
  02  |  01        |   bbbb   |
  03  |  02        |   cccc   |
  04  |  02        |   dddd   |
  05  |  03        |   eeee   |
  06  |  03        |   ffff   |
  07  |  03        |   gggg   |
  </code></pre>
  
  <p>the same for the photos:</p>
  
  <pre><code>ID  |  USER      |  PHOTO   |
  01  |  01        |   aaaa   |
  02  |  02        |   cccc   |
  03  |  02        |   dddd   |
  04  |  03        |   eeee   |
  05  |  03        |   ffff   |
  06  |  03        |   gggg   |
  </code></pre>
  
  <p>now, i'm trying to order the results of the first table, on the <code>STATUS</code> field, so the users with status 2 will go at the end (or beginning) of the results, but i would like to order the others by the fact that they have, or have not videos or photos. Actually for all the users in the status 1 I check if they have video AND photos and display a message like "warning, you have no videos" or something, i would like also to order all the users on the status basis AND on the fact that they have or not photos or video... something like:</p>
  
  <pre><code>SELECT id,username,status,NumOfPhotos,NumOfVideos order by status ASC, numOfPhotos ASC, NumOfVideos DESC
  </code></pre>
  
  <p>where <code>NumOfPhotos</code>, for example is <code>SELECT COUNT(*) FROM PHOTOS WHERE USER = USERS.ID</code></p>
  
  <p>Is that possible? is that possible in a single statement?</p>