title: MySQL 4 Table Query Posts with images always at the top
body: <p>I have 4 MySQL tables</p>
  
  <p>folder, folder_users, folder_posts, posts</p>
  
  <p>I need to build a view which queries the above and returns data which I can the filter based on a user id</p>
  
  <p>folder has:</p>
  
  <blockquote>
    <p>id, userid, title, date</p>
  </blockquote>
  
  <p>folder_users has:</p>
  
  <blockquote>
    <p>id, folderid, userid</p>
  </blockquote>
  
  <p>folder_posts has:</p>
  
  <blockquote>
    <p>id, folderid, postid</p>
  </blockquote>
  
  <p>posts has:</p>
  
  <blockquote>
    <p>id, userid, media, text, type</p>
  </blockquote>
  
  <p>The aim is to display a set of folders to a logged in user where they have access to the folder and that shows the latest image (held in media column on posts table) ideally I would like to get this into a MySQL View and have it ordered by folder.date</p>
  
  <p>I am struggling as where to start with this query so any help would be really great thanks.</p>
  
  <p><strong>* EDIT just thought of a missing element **</strong></p>
  
  <p>The posts table will have other type of posts that will mean that some posts in a folder may have no media, so the query needs to compensate for this i.e. where there is media make sure that this included in the result and where no media then don't </p>
  
  <hr>