The query below signifies things i am attempting to do, I have to pull in a listing of blog_posts as well as join having a customers table.

What it's also doing is tugging inside a random 'picture_filename' from blog_updates_pictures. It requires blog_updates like a join to reference your blog_update_id.

What Let me do now's also COUNT the amount of blog_updates for every blog_publish. I believe this can be a subquery but every implementation fails. It might be also good to achieve the count accept arguments (ie. blog_updates where date = ?). Also, there might be no updates or pictures to some blog_publish.

$select = $db->select ();
$select->from ( array ('b' => 'blog_posts' ), array('headline', 'date_created'));
$select->join ( array ('u' => 'users' ), 'u.user_id = b.user_id', array ( 'email' ) );
$select->joinLeft ( array ('bu' => 'blog_updates' ), 'bu.blog_id = b.blog_id', array () );
$select->joinLeft ( array ('bup' => 'blog_updates_pictures' ), 'bu.blog_update_id = bup.blog_update_id', array ('picture_filename' ) );

Can someone show me the way in which?

Thanks

What Let me do now's also COUNT the amount of blog_updates for every blog_publish.

You are able to make that happen using aggregation - use GROUP BY bu.blog_id, so that as additional column COUNT(bu.blog_id) AS blog_updates_count. It will work.

Create subselects as:

$subselect = $db->select()
  ->from(
    array ('bu' => 'blog_updates' ),
    array(
      'blog_id', 
      'updates' => 'count(*)' 
    )
  )
  ->group("bu.blog_id");

After which join the $subselect together with your primary $choose as:

$select->join(
  array( 't' => $subselect),
  "t.blog_id = b.blog_id",
  array( 'updates' )
);

When we had the table structure you can find a far more complete answer