Why does this error 1066 show up from join statesment

Question

I have yet another issue. I am trying to join 2 tables so that i can pull data from both. I am new to TableGateway and am having issues finding the same situation. I have a page that will pull blogs by state and i also have a state table. I want to join these using state_id and show "state" from state table on the page. Below is what i have. Not sure if i am even using the right code!

Model - From StateTable:

public function getState($state)
{
$sqlSelect = $this->tableGateway->getSql()->select();
$sqlSelect->columns(array('state_id'));
$sqlSelect->join('states', 'states.state_id = state_id', array('state'), 'inner');

$statement =   $this->tableGateway->getSql()->prepareStatementForSqlObject($sqlSelect);
$resultSet = $statement->execute();
return $resultSet;

}

Model from PostTable:

   public function getbystate($state_id)
   {
    $state_id  = (int) $state_id;
    $rowset = $this->tableGateway->select(array('state_id' => $state_id));

    return $rowset;
   }

Controller action:

 public function ListAction()
 {
  $state_id = $this->params()->fromRoute('state_id');
  $state = $this->params('state');
  $list = $this->getPostsTable()->getbystate($state_id);
  $states = $this->getStatesTable()->getState($state);
  $view = new ViewModel(array(
    'list' => $list,
    'states' => $states,
  ));

  return $view;  
 }

Update 8:41 EST 06/06/2017

I put the join in the Posts module instead of in the States Model. I have gotten rid of the errors however I still need to know how to show the state. Everything above is the same except it resides in the posts module.

The "View" is below - it errors Notice: Undefined property: Zend\Db\Adapter\Driver\Pdo\Result::$state in /var/www/html/module/Blog/view/blog/list.phtml on line 6

View:

 $title = 'My Blog';
 $this->headTitle($title);
 ?>
 <h1><?php echo $this->escapeHtml($title); ?></h1>
 <td><?php echo $this->escapeHtml($states->state);?></td>
 <table class="table">
 <tr>
  <th>Title</th>
  <th>View</th>
  <th>Comments</th>
  <th>Post Date</th>

 </tr>
 <?php foreach ($list as $posts) : ?>
 <tr>
   <td>
     <a href="/Blog/view/<?php echo $this->escapeHtml($posts->post_id);?>">
     <?php echo $this->escapeHtml($posts->post_title);?>
   </a>
 </td>
 <td><?php echo $this->escapeHtml($posts->num_views);?></td>
 <td><?php echo $this->escapeHtml($posts->num_comments);?></td>
 <td><?php echo $this->escapeHtml($posts->post_date);?></td>

 </tr>
 <?php endforeach; ?>
 </table>  

Added tables 12:01 EST 06/07/2017 Post Table:

 CREATE TABLE `posts` (
 `post_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `state_id` smallint(6) DEFAULT NULL,
 `created_user_id` int(11) DEFAULT NULL,
 `post_title` varchar(255) NOT NULL,
 `post_date` datetime DEFAULT NULL,
 `is_active` tinyint(4) NOT NULL,
 `status` enum('deleted','draft','inactive','active') DEFAULT 'inactive',
 `activate_date` datetime DEFAULT NULL,
 `is_hot` tinyint(4) DEFAULT '0',
 `ordering` int(11) DEFAULT '0',
 `num_views` int(11) DEFAULT '0',
 `allow_comment` tinyint(4) DEFAULT NULL,
 `num_comments` int(11) DEFAULT '0',
 `picLink` varchar(50) DEFAULT NULL,
 `description` varchar(1000) DEFAULT NULL,
 `food` enum('Yes',' No') DEFAULT NULL,
 PRIMARY KEY (`post_id`),
 KEY `idx_latest` (`status`,`activate_date`),
 KEY `idx_latest_category` (`state_id`,`status`,`activate_date`),
 KEY `idx_most_commented` (`state_id`,`status`,`num_comments`) USING BTREE,
 KEY `idx_most_viewed` (`state_id`,`status`,`num_views`) USING BTREE,
 KEY `idx_most_viewed_2` (`status`,`num_views`),
 KEY `idx_created_user` (`created_user_id`,`post_id`)

States Table:

 CREATE TABLE `states` (
 `state_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `state` varchar(45) NOT NULL,
 PRIMARY KEY (`state_id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=utf8;   

Answer 1

You do not need to join posts and states tables to show state according to your list.phtml. You are trying to output state there like $states->state therefore, this is completely outside loop. So you need to fetch an object from which you can output state the way you wanted. Make another method in the StatesTable model to get it worked. See the following

public function getStateById($state_id)
{
    $state_id  = (int) $state_id;
    $rowset = $this->tableGateway->select(array('state_id' => $state_id));
    $row = $rowset->current();

    if (!$row) {
        throw new \Exception("Could not find row $state_id");
    }

    return $row;
}

And getbystate($state_id) method from PostsTable model should be unchanged like you wrote above. listAction() method should be the following

public function ListAction()
{
    $state_id = $this->params()->fromRoute('state_id');

    $list = $this->getPostsTable()->getbystate($state_id);
    $states = $this->getStatesTable()->getStateById($state_id);

    $view = new ViewModel(array(
        'list' => $list,
        'states' => $states,
    ));

    return $view;  
}

Now as I told your sql query for joining two tables was not right, that would like this

public function getState($state)
{
    $state = (string) $state;
    $sqlSelect = $this->tableGateway->getSql()->select();

    // Specify columns from 'posts' table
    $sqlSelect->columns(array('post_title', 'description', 'etc'));

    // Here you can specify columns for 'states' table
    $sqlSelect->join('states', 'posts.post_id = states.state_id', array('state', 'etc'), 'inner');

    // Apply conditions here
    $sqlSelect->where(array('states.state' => $state));

    $resultSet = $this->tableGateway->selectWith($sqlSelect);      

    return $resultSet;

}

This method has a limitation. You cant convert this resultset object to array because of result-set prototype issue. But this will work somehow.