title: mySQL select from one table where does not exists in another, and is not a child in a third table
body: <p>I'm using Yii RBAC to control user access to my app, it consists of three mySql tables;</p>
  
  <p><code>authitem</code> (the RBAC role items)</p>
  
  <p><code>authitemchild</code> (any RBAC rules that belong to other roles)</p>
  
  <p><code>authitemassignment</code> (assigning roles to a user)</p>
  
  <p>For example we may have tables like so:</p>
  
  <p>authitem:</p>
  
  <pre><code>|   name (pk)   |
   areaASuperUser
   areaACreateOnly
   areaAReadOnly
   areaAUpdateOnly
   areaADeleteOnly
  
   areaBSuperUser
   areaBCreateOnly
   areaBReadOnly
   areaBUpdateOnly
   areaBDeleteOnly
  </code></pre>
  
  <p>authitemchild</p>
  
  <pre><code>|   parent (pk)   |   child (pk)   |
   areaASuperUser    areaACreateOnly
   areaASuperUser    areaAReadOnly
   areaASuperUser    areaAUpdateOnly
   areaASuperUser    areaADeleteOnly
  
   areaBSuperUser    areaBCreateOnly
   areaBSuperUser    areaBReadOnly
   areaBSuperUser    areaBUpdateOnly
   areaBSuperUser    areaBDeleteOnly
  </code></pre>
  
  <p>auithitemassignment</p>
  
  <pre><code>|    itemname (pk)   |   userid (pk)   |
   areaASuperUser       1
  </code></pre>
  
  <p>In the above scenario, user with id of 1 has complete CRUD access in areaA. What I need is a list of all the roles the user <em>does not</em> have access too, and I need to take into account the children of any roles they have access too as well.</p>
  
  <p>I can easily grab all the roles the user doesn't have access to:</p>
  
  <pre><code>SELECT DISTINCT `ai`.`name`
  FROM `authitem` `ai`
  LEFT JOIN `authassignment` `aa`
  ON `aa`.`itemname` = `ai`.`name` AND `aa`.`userid` = 1
  WHERE  `aa`.`itemname` IS NULL
  </code></pre>
  
  <p>But this returns:</p>
  
  <pre><code> |   name    |
   areaACreateOnly
   areaAReadOnly
   areaAUpdateOnly
   areaADeleteOnly
  
   areaBSuperUser
   areaBCreateOnly
   areaBReadOnly
   areaBUpdateOnly
   areaBDeleteOnly
  </code></pre>
  
  <p>And because all the areaA* roles are children of areaASuperUser, I don't want them returned.</p>
  
  <p>Any advice or a push in the right direction would be greatly appreciated!</p>
  
  <p><em>*</em> Edit: </p>
  
  <p>Thanks @SuVeRa, your answer:</p>
  
  <pre><code>SELECT DISTINCT `ai`.`name`
  FROM `authitem` `ai`
  LEFT JOIN `authassignment` `aa`
  ON `aa`.`itemname` = `ai`.`name` AND `aa`.`userid` = 1
  WHERE  
  `aa`.`itemname` IS NULL 
  AND `ai`.`name` NOT IN (
      SELECT
      `aic`.`child` itemname
      FROM `authitemchild` `aic`
      JOIN `authassignment` `aa`
      ON `aa`.`itemname` = `aic`.`parent`
      WHERE `aa`.`userid` = 1
  )
  </code></pre>
  
  <p>works perfectly for the example above, however I've just extended the app to include more than one level for children, i.e.</p>
  
  <p>authitem:</p>
  
  <pre><code>|   name (pk)   |
  
   areaABSuperUser
  
   areaASuperUser
   areaACreateOnly
   areaAReadOnly
   areaAUpdateOnly
   areaADeleteOnly
  
   areaBSuperUser
   areaBCreateOnly
   areaBReadOnly
   areaBUpdateOnly
   areaBDeleteOnly
  
   areaCSuperUser
   areaCCreateOnly
   areaCReadOnly
   areaCUpdateOnly
   areaCDeleteOnly
  </code></pre>
  
  <p>authitemchild</p>
  
  <pre><code>|   parent (pk)   |   child (pk)   |
   areaABSuperUser   areaASuperUser
   areaABSuperUser   areaBSuperUser
  
   areaASuperUser    areaACreateOnly
   areaASuperUser    areaAReadOnly
   areaASuperUser    areaAUpdateOnly
   areaASuperUser    areaADeleteOnly
  
   areaBSuperUser    areaBCreateOnly
   areaBSuperUser    areaBReadOnly
   areaBSuperUser    areaBUpdateOnly
   areaBSuperUser    areaBDeleteOnly
  
   areaCSuperUser    areaCCreateOnly
   areaCSuperUser    areaCReadOnly
   areaCSuperUser    areaCUpdateOnly
   areaCSuperUser    areaCDeleteOnly
  </code></pre>
  
  <p>auithitemassignment</p>
  
  <pre><code>|    itemname (pk)   |   userid (pk)   |
   areaABSuperUser      1
  </code></pre>
  
  <p>The original answer would return;</p>
  
  <pre><code> |   name    |
   areaACreateOnly
   areaAReadOnly
   areaAUpdateOnly
   areaADeleteOnly
  
   areaBCreateOnly
   areaBReadOnly
   areaBUpdateOnly
   areaBDeleteOnly
  
   areaCSuperUser
   areaCCreateOnly
   areaCReadOnly
   areaCUpdateOnly
   areaCDeleteOnly
  </code></pre>
  
  <p>As it's filtering out the parent and child, but not the children of children. The result I'm after (in this second example) is:</p>
  
  <pre><code> |   name    |
   areaCSuperUser
   areaCCreateOnly
   areaCReadOnly
   areaCUpdateOnly
   areaCDeleteOnly
  </code></pre>
  
  <p>***Edit 2:</p>
  
  <p>Specifically for Yii:</p>
  
  <p>I've done some more reading into Yii, and <a href="http://www.yiiframework.com/doc/api/1.1/CAuthManager" rel="nofollow">CAuthManager</a> has a number of helpful methods, including <a href="http://www.yiiframework.com/doc/api/1.1/IAuthManager#hasItemChild-detail" rel="nofollow">hasItemChild()</a>, <a href="http://www.yiiframework.com/doc/api/1.1/IAuthManager#isAssigned-detail" rel="nofollow">isAssigned()</a>, <a href="http://www.yiiframework.com/doc/api/1.1/IAuthManager#getItemChildren-detail" rel="nofollow">getItemChildren()</a>, ... and others, that could help with grabbing data like I need</p>