<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>

CC BY-SA 3.0
2017-01-27T21:20:32.513Z
2017-01-27T21:30:15.353Z
2017-01-27T21:30:15.353Z
Brad Bell
<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>

CC BY-SA 3.0
2017-01-27T21:47:56.267Z
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.
Adam McCombs
<plugin-development><yii><join>
Joining two plugin tables by reference Id causes duplicates
# 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/>


---

