<p>One option would be to use <code>leftJoin()</code> instead of <code>join()</code>, which will still return the record if the joining table row doesn't exist. The values will be joined as <code>NULL</code>.</p>

<blockquote>
  <p>The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.</p>
</blockquote>

<p><a href="http://www.w3schools.com/sql/sql_join_left.asp" rel="nofollow">http://www.w3schools.com/sql/sql_join_left.asp</a></p>

<p>If you needed more control, you could always incorporate some aggregate functions in SQL to do a count an the records found, and you could perform additional conditional statements with the <code>HAVING</code>, instead of <code>WHERE</code> (which won't work) since aggregate data isn't stored in the db.</p>

CC BY-SA 3.0
2015-02-16T19:39:03.430Z
2015-02-16T19:39:03.430Z
Justin Kimbrell
<p>Ok, I need a MySQL pro.</p>

<p>The setting:</p>

<ul>
<li>Hundreds of users</li>
<li>User can be a child of other users, through relation field 'myParents'</li>
<li>I need a list of users, and their child(ren), related to an entry (through relation field <code>belongsToGroups</code>)</li>
</ul>

<p>I don't want to use <code>$criteria-&gt;find()</code>, because it returns objects, which makes it terribly slow. So, I want to create a db query. What I have now, is this:</p>

<pre><code>$parentsWithChildren = craft()-&gt;db-&gt;createCommand()
        -&gt;select('u.id userId, u.firstName parentFirstName, u.lastName parentLastName, c.firstName childFirstName, c.lastName childLastName')
        -&gt;from('users u')
        -&gt;join('relations r', 'u.id = r.sourceId')
        -&gt;join('relations uc', 'u.id = uc.targetId')
        -&gt;join('usergroups_users g', 'u.id = g.userId')
        -&gt;join('users c', 'uc.sourceId = c.id')
        -&gt;where('r.targetId = '.$elementId)
        -&gt;andWhere('uc.fieldId = 92')
        -&gt;andWhere('g.groupId != 5')
        -&gt;order('u.id')
        -&gt;queryAll();
</code></pre>

<p>This return a nice list of all users, children included, but only if a user has children. <strong>In short:</strong> I also need the users without children. Any SQL guru willing to help me out, it's been a long day ;)</p>

<plugin-development>
Selecting all parent users and their related children
# Selecting all parent users and their related children

(Asked by: username_0 on 2015-02-16)

Ok, I need a MySQL pro.
The setting:

Hundreds of users
User can be a child of other users, through relation field 'myParents'
I need a list of users, and their child(ren), related to an entry (through relation field belongsToGroups)

I don't want to use $criteria->find(), because it returns objects, which makes it terribly slow. So, I want to create a db query. What I have now, is this:
$parentsWithChildren = craft()->db->createCommand()
        ->select('u.id userId, u.firstName parentFirstName, u.lastName parentLastName, c.firstName childFirstName, c.lastName childLastName')
        ->from('users u')
        ->join('relations r', 'u.id = r.sourceId')
        ->join('relations uc', 'u.id = uc.targetId')
        ->join('usergroups_users g', 'u.id = g.userId')
        ->join('users c', 'uc.sourceId = c.id')
        ->where('r.targetId = '.$elementId)
        ->andWhere('uc.fieldId = 92')
        ->andWhere('g.groupId != 5')
        ->order('u.id')
        ->queryAll();

This return a nice list of all users, children included, but only if a user has children. In short: I also need the users without children. Any SQL guru willing to help me out, it's been a long day ;)

**Tags:** plugin-development

---

## 1 answers

### Answer by username_1 on 2015-02-16. Score: 2 | Accepted answer

One option would be to use `leftJoin()` instead of `join()`, which will still return the record if the joining table row doesn't exist. The values will be joined as `NULL`.

> The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.

<http://www.w3schools.com/sql/sql_join_left.asp>

If you needed more control, you could always incorporate some aggregate functions in SQL to do a count an the records found, and you could perform additional conditional statements with the `HAVING`, instead of `WHERE` (which won't work) since aggregate data isn't stored in the db.


---

