title: Yii CDBCriteria Complex Join Query
body: <p>I have 3 tables:</p>
  
  <ol>
  <li>bank</li>
  <li>bank_details</li>
  <li>bank_bank_details</li>
  </ol>
  
  <p>Relation between them:</p>
  
  <p><strong>bank model:</strong></p>
  
  <pre><code>public function relations() {
          return array(
              'bankBankDetails' =&gt; array(self::HAS_MANY, 'BankBankDetails', 'bank_id'),
          );
  }
  </code></pre>
  
  <p><strong>bank_details model:</strong></p>
  
  <pre><code>public function relations() {
          return array(
              'bankBankDetails' =&gt; array(self::HAS_MANY, 'BankBankDetails', 'bank_details_id'),
          );
      }
  </code></pre>
  
  <p><strong>bank_bank_details model:</strong> </p>
  
  <pre><code>public function relations()
      {
          return array(
              'bank' =&gt; array(self::BELONGS_TO, 'Bank', 'bank_id'),            
              'bankDetails' =&gt; array(self::BELONGS_TO, 'BankDetails', 'bank_details_id'),
          );
      }
  </code></pre>
  
  <p>I want to fetch bank details like bank_name, ifsc etc info in admin view of <strong>bank_details</strong> model.</p>
  
  <p>The normal SQL query I have generated which works perfectly fine:</p>
  
  <pre><code>SELECT b.name
  FROM bank b
  LEFT JOIN bank_bank_details bbd ON bbd.bank_id = b.bank_id
  LEFT JOIN bank_details bd ON bd.bank_details_id = bbd.bank_details_id
  WHERE bd.bank_details_id = bbd.bank_details_id
  LIMIT 0 , 30
  </code></pre>
  
  <p>Now I just wants to integrate this with Yii CDBCriteria but its not working for me. Please check the code below:</p>
  
  <pre><code>public function search() {
          $criteria = new CDbCriteria;    
  //        select b.name
  //        from bank b
  //        left join bank_bank_details bbd
  //        on bbd.bank_id = b.bank_id
  //        left join bank_details bd on bd.bank_details_id = bbd.bank_details_id
  //        WHERE bd.bank_details_id = bbd . bank_details_id;
  
          $criteria-&gt;compare('bank_details_id', $this-&gt;bank_details_id);
          $criteria-&gt;compare('first_holder_name', $this-&gt;first_holder_name, true);
          $criteria-&gt;compare('nominee1', $this-&gt;nominee1, true);
          $criteria-&gt;select = 'b.name';
          $criteria-&gt;join = 'LEFT JOIN bank_bank_details bbd ON bbd.bank_id = b.bank_id ';
          $criteria-&gt;join .= 'LEFT JOIN bank_details bd ON bd.bank_details_id = bbd.bank_details_id';
          $criteria-&gt;condition = 'bd.bank_details_id = bbd.bank_details_id';
  
          return new CActiveDataProvider($this, array(
              'criteria' =&gt; $criteria,
              'pagination' =&gt; array(
                  'pageSize' =&gt; 10,
              ),
          ));
      }
  </code></pre>
  
  <p><strong>Error:</strong>
          <strong>Error 500 Found
          CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'b.bank_id' in 'on clause'</strong></p>
  
  <p>Any help would be appreciated.</p>