title: yii multiple inner joins
body: <p>I am trying to write a query in yii. I have the following which works</p>
  
  <pre><code>$criteria = new CDbCriteria;
  $criteria-&gt;condition = "($column = :id)";
  $criteria-&gt;params = array(":id" =&gt;  $id );
  $rows = Jobs::model()-&gt;with('pROJ')-&gt;findAll($criteria);
  </code></pre>
  
  <p>This returns the model of Jobs in array. I need to write the following query in yii to return a model</p>
  
  <pre><code>SELECT jobs.JOBNO, jobs.STATUS, projects.ORDERNO, jobs.PROJID, jobs.NAME, jobs.SEQ, jobs.PCENTDONE, jobs.EARNED, jobs.VALUE, jobs.DATEIN, jobs.DATEDONE, jobs.DATEDUE, jobs.SENTBACK, jobs.ORIGTAPES, jobs.COMMENTS, projects.CATEGORY, orders.BIDNO
  FROM (jobs INNER JOIN projects ON jobs.PROJID = projects.PROJID) INNER JOIN orders ON projects.ORDERNO = orders.ORDERNO
  where jobs.projid =     3002001
  ORDER BY jobs.JOBNO, jobs.PROJID 
  </code></pre>
  
  <p>I have tried the following but it does not work</p>
  
  <pre><code>$rows = Yii::app()-&gt;db-&gt;createCommand()
              -&gt;select('jobs.*, projects.ORDERNO, projects.CATEGORY, orders.BIDNO')
              -&gt;from('jobs, projects, orders')
              -&gt;join('projects p','jobs.PROJID = p.PROJID')
              -&gt;join('orders o', 'p.ORDERNO = o.ORDERNO')
              -&gt;where('jobs.projid=:id', array(':id'=&gt;$id))
              -&gt;queryRow(); 
  </code></pre>
  
  <p>I get the following error</p>
  
  <pre><code>CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'jobs.PROJID' in 'on clause'. The SQL statement executed was: SELECT `jobs`.*, `projects`.`ORDERNO`, `projects`.`CATEGORY`, `orders`.`BIDNO`
  FROM `jobs`, `projects`, `orders`
  JOIN `projects` `p` ON jobs.PROJID=p.PROJID
  JOIN `orders` `o` ON p.ORDERNO=o.ORDERNO
  WHERE jobs.projid=:id 
  </code></pre>
  
  <p>I have updated to </p>
  
  <pre><code>$rows = Yii::app()-&gt;db-&gt;createCommand()
                  -&gt;select('jobs.*, projects.orderno, projects.category, orders.bidno')
                  -&gt;from('jobs')
                  -&gt;join('projects p','jobs.projid = p.projid')
                  -&gt;join('orders o', 'p.orderno = o.orderno')
                  -&gt;where('jobs.projid=:id', array(':id'=&gt;$id))
                  -&gt;queryRow();  
  </code></pre>
  
  <p>but i still get the error. All columns in mysql are CAPS</p>
  
  <pre><code>CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'projects.orderno' in 'field list'. The SQL statement executed was: SELECT `jobs`.*, `projects`.`orderno`, `projects`.`category`, `orders`.`bidno`
  FROM `jobs`
  JOIN `projects` `p` ON jobs.projid = p.projid
  JOIN `orders` `o` ON p.orderno = o.orderno
  WHERE jobs.projid=:id 
  </code></pre>