title: trying to create a 'join' statement using Zend DB Table - zend framework 2
body: <p>I have a model that needs to execute a join query on 2 tables... lets call them friend_list and user_profile.</p>
  
  <p>I am having a heck of a time trying to put together the zend style code to produce the proper query I need to accomplish this... below is the desired query:</p>
  
  <pre><code>SELECT friend_list.friend_id, user_profile.id, user_profile.username
  FROM `friend_list`
  INNER JOIN `user_profile`
  ON friend_list.friend_id = user_profile.id
  where user_id = 1
  </code></pre>
  
  <p>Here is my model_friends</p>
  
  <pre><code>&lt;?php
  //model created to add user to database, sendmail etc...
  require_once 'Zend/Db/Table/Abstract.php';
  
  class Model_Friends extends Zend_Db_Table_Abstract
  {
  protected $_name = "friend_list";
  
  public function fetchFriendList($userID)
  {
  
      $accountsTable = array('up' =&gt; 'user_profile');
      $select = $this-&gt;select()
                     -&gt;from($this-&gt;_name)
                     -&gt;join($accountsTable, 'up.id = friend_List.friend_id', array())
                     -&gt;where("up.id = ?", $userID);
  
      $result = $this-&gt;fetchAll($select);
  
  
      if ($result !== null){
          echo $select;
          return $result;
      } else {
          echo "no records found";
      }   
    }
  }
  </code></pre>
  
  <p>the above model produces the follow SQL statement which is NOT what I want... </p>
  
  <pre><code>SELECT `friend_list`.* 
  FROM `friend_list` 
  INNER JOIN `user_profile` 
  AS `up` 
  ON up.id =     friend_List.friend_id 
  WHERE (up.id = '1') 
  </code></pre>
  
  <p>adding the table structures as requested:</p>
  
  <pre><code>DROP TABLE IF EXISTS `buzz`.`friend_list`;
  CREATE TABLE  `buzz`.`friend_list` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `friend_id` int(11) NOT NULL,
  `approved_timestamp` date NOT NULL,
  `status` varchar(15) DEFAULT 'pending',
  PRIMARY KEY (`id`) USING BTREE
  ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
  
  DROP TABLE IF EXISTS `buzz`.`user_profile`;
  CREATE TABLE  `buzz`.`user_profile` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mob` varchar(50) NOT NULL DEFAULT 'no info',
  `dob` varchar(50) NOT NULL DEFAULT '',
  `yob` varchar(50) NOT NULL DEFAULT '',
  `language` varchar(75) NOT NULL DEFAULT 'English',
  `gender` varchar(25) NOT NULL DEFAULT 'no info',
  `about` varchar(1000) NOT NULL DEFAULT 'no info',
  `country` varchar(45) NOT NULL DEFAULT 'no info',
  `username` varchar(45) NOT NULL,
  PRIMARY KEY (`id`,`username`) USING BTREE
  ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
  </code></pre>