Answers.Body: <p>You're probably looking for an <code>inner join</code> (which Yii just calls a <a href="http://www.yiiframework.com/doc/api/1.1/CDbCommand#join()-detail" rel="nofollow noreferrer">join</a>)instead of a <code>leftJoin</code>: <a href="https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/" rel="nofollow noreferrer">https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/</a></p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-01-27T21:20:32.513Z
Answers.LastActivityDate: 2017-01-27T21:30:15.353Z
Answers.LastEditDate: 2017-01-27T21:30:15.353Z
Answers.UserDisplayName: Brad Bell
Body: <p>I'm running a query in which I need to join two tables <strong>table_views</strong> and <strong>table_clicks</strong>. Both tables share several column names (mainly refId, month, year and total).</p>
  
  <p>However when I try and join these tables together, instead of returning ~18 or 20 rows/records. It repeats the join for every single row, producing 360 records.</p>
  
  <p>How can I query and combine both tables by their reference Id?</p>
  
  <pre><code>        $records = craft()-&gt;db-&gt;createCommand()
          -&gt;select('*')
          -&gt;from('table_views table_views')
          -&gt;leftJoin('table_clicks table_clicks', 'table_views.refId=table_clicks.refId')
          -&gt;where('table_views.refId = :refId', array(':refId' =&gt; $id))
          -&gt;query();
  </code></pre>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-01-27T21:47:56.267Z
Comments.Text: Leaving this up as a valid question but this was certainly user error. The query ran as intended but because we ran the where looking for Id, it looped through each time because we didn't re-define the month/year.
Comments.UserDisplayName: Adam McCombs
Tags: <plugin-development><yii><join>
Title: Joining two plugin tables by reference Id causes duplicates
ThreadText: # Joining two plugin tables by reference Id causes duplicates
  
  (Asked by: username_0 on 2017-01-27)
  
  I'm running a query in which I need to join two tables **table\_views** and **table\_clicks**. Both tables share several column names (mainly refId, month, year and total).
  
  However when I try and join these tables together, instead of returning ~18 or 20 rows/records. It repeats the join for every single row, producing 360 records.
  
  How can I query and combine both tables by their reference Id?
  
  ```
          $records = craft()->db->createCommand()
          ->select('*')
          ->from('table_views table_views')
          ->leftJoin('table_clicks table_clicks', 'table_views.refId=table_clicks.refId')
          ->where('table_views.refId = :refId', array(':refId' => $id))
          ->query();
  
  ```
  
  **Tags:** plugin-development, yii, join
  
  > **Comments:**
  > **username_0**: Leaving this up as a valid question but this was certainly user error. The query ran as intended but because we ran the where looking for Id, it looped through each time because we didn't re-define the month/year.  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2017-01-27. Score: 3 | Accepted answer
  
  You're probably looking for an `inner join` (which Yii just calls a [join](http://www.yiiframework.com/doc/api/1.1/CDbCommand#join()-detail))instead of a `leftJoin`: <https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/>
  
  
  ---
  
  