Limit the results returned by 'published' I am new to the Database API and SQL queries in general. I needed to create a custom module that could be given a list of fields and would output the average value of each of these fields. I am pleased to have got my module to do this. 

Unfortunately it includes all of the values for my fields, irregardless of whether they belong to a published node or not. To get it to exclude results from unpublished nodes I'm guessing that my query needs to draw in information from the node table. Could someone please point me in the right direction of how to exclude results from unpublished nodes?

Please find my module code below.

    function custom_aggregate_data_block_info() {
  // This example comes from node.module.

   $blocks['custom_data'] = array(
    'info' =&gt; t('Custom Data'),
// DRUPAL_CACHE_PER_ROLE will be assumed.
  ); 

  return $blocks;

}


/**
* Implements hook_block_view().
*
* Prepares the contents of the block.
*/

function custom_aggregate_data_block_view($delta = '') {
  // This example is adapted from node.module.
  $block = array();

  switch ($delta) {

      case 'custom_data':


    // initial output for block
        $block['subject'] = t('feild test');        
        $blockoutput = "&lt;div id='results'&gt;";

    // searches the database and displays the results in a block

      /* database table name, table cell name */
      /* ie DRUPAL_MACH_NAME_value, field_data_DRUPAL_MACH_NAME */
      $feilds_to_query = array ("field_test_2004","field_test_2005");

      foreach ($feilds_to_query as $dbfeild) {
          // Do something with each $record
          $dbfeild_tablename = $dbfeild + "_value";
          $dbfeild_cellname = "field_data_" + $dbfeild;
          $querystring = "SELECT " + $dbfeild_tablename + " FROM " + $dbfeild_cellname;
          $query =  db_query($querystring);

          $total_field_value = 0;
          $total_records_field_value = 0;
          $average_records_field_value = 0;

          foreach ($query as $record) {
              // Do something with each $record
              $total_field_value = $total_field_value + $record-&gt;$dbfeild_tablename;
              $total_records_field_value = $total_records_field_value + 1;
          }

          // when looping through the results is finished then average the total by the number of records returned.
        $average_records_field_value = $total_field_value/$total_records_field_value ;  

        // add the averaged data to the values stored ready to be returned
        $blockoutput .= "&lt;div&gt;Average results: " . $dbfeild . ": " . $average_records_field_value . "&lt;/div&gt;";

       }

       // when finished looping through all the queries then close the html div, then return the results as a block 
       $blockoutput .= "&lt;/div&gt; &lt;!--end #results --&gt;";
       $block['content'] = $blockoutput ;


    break;
  }
  return $block;
}


Note that I have the case statement included as I plan to make use of several blocks in the completed module.
The node table has a field called status - 1 is published, 0 is unpublished.

Are field_test_2004 and field_test_2005 Drupal generated tables?

Normally table names take the form of field_data_field_test_2004...

Anyway, assuming they are Drupal generated tables they will have a field called entity_id which you can join to the node table with.

If you are building queries like this I would suggest you look into using Drupal Dynamic Queries as opposed to string concatenation - it is much easier to add bits to and manipulate the query.

Also feilds is spelt fields (sorry, couldn't help myself)