title: SQL request with a joining condition
body: <pre><code>SELECT mairie.city, permission.permission
      FROM permission , user_mairie, mairie
      WHERE user_mairie.iduser = 1
      AND user_mairie.idmairie = mairie.idmairie
      CASE user_mairie.idrole
          WHEN 2 THEN 
              JOIN user_permission 
                ON user_permission.idpermission = permission.idpermission 
               AND user_permission.iduser = user_mairie.iduser
      END
      ORDER BY mairie.idmairie, permission.idpermission
  </code></pre>
  
  <p>I'm trying to get the permissions of a specific user if this user has a specific role.</p>
  
  <p>A "mairie" is a "town hall" in french.</p>
  
  <p>A user can have different roles in different "mairie"s.</p>
  
  <p>If the user on user_mairie has idrole = 2, then we have to go to the table "user_permission" to get it's permissions.</p>
  
  <p>If the user on user_mairie has idrole = 1 then he is admin and he has ALL permissions, but the permissions are not written in user_permission (because user_permission is used only for idrole = 2).</p>
  
  <p><strong>What I want is for example :</strong></p>
  
  <p>if the user_mairie.idrole = 1 :</p>
  
  <pre><code>SELECT * 
  FROM permission
  </code></pre>
  
  <p>if the user_mairie.idrole = 2</p>
  
  <pre><code>SELECT * 
  FROM permission, user_permission 
  WHERE user_mairie.idrole = 2 
  AND user_mairie.iduser = user_permission.iduser
  AND user_permission.idpermission = permission.idpermission
  </code></pre>
  
  <p>I could do this using my programming language and making 2 requests, but I'd like to know if in pure SQL, this issue is solvable.</p>
  
  <p>Some datas:</p>
  
  <p>Table permission:</p>
  
  <pre><code>idpermission | permission
  1            | permission_1
  2            | permission_2
  3            | permission_3
  </code></pre>
  
  <p>Table user_mairie :</p>
  
  <pre><code>iduser     | idmairie    | idrole
  1          | 1           | 1
  1          | 2           | 2
  </code></pre>
  
  <p>Table user_permission : </p>
  
  <pre><code>iduser     | idpermission  | idmairie
  1          | 1             | 2
  1          | 3             | 2
  </code></pre>
  
  <p>Table mairie :</p>
  
  <pre><code>idmarie     | city
  1           | mairie_1
  2           | mairie_2
  </code></pre>
  
  <p>The result I want (for a given iduser = 1) would be : </p>
  
  <pre><code>mairie_1 : permission_1, permission_2, permission_3
  mairie_2 : permission_1, permission_3
  </code></pre>
  
  <p>Thanks for reading me.</p>