title: SQL - Getting the authorisation levels of employees for certain projects
body: <p>I am working on a web app for our company's employees. Based on certain training that they had and procedures that they read, a certain employee can be authorised to work in a certain project.</p>
  
  <p>I have several tables:</p>
  
  <ul>
  <li>vw_Employee (stores all the employee records and has a primary key for identification called "Sicil")</li>
  <li>tbl_yet_Proje (stores all the projects available in the company at that time and has a primary identification key called "Proj_ID")</li>
  <li>tbl_yet_ProjeEgitim (stores training prerequisites for a given project, stores two identification keys: one is Proj_ID and the other is TR_ID to identify which training is needed for the corresponding project)</li>
  <li>tbl_yet_ProjeProsedur (just like tbl_yet_ProjeEgitim this one stores procedure prerequisites for a given project, stores two identification keys: one is Proj_ID and the other is Pros_ID to identify which procedure is needed for the corresponding project)</li>
  <li>tbl_yet_EmployeeEgitim and tbl_yet_EmployeeProsedur (just like tbl_yet_ProjeEgitim and tbl_yet_ProjeProsedur these two stores which employee took which training and read which procedure respectively. They store two identification keys each: "Sicil" to identify the employee and TR_ID and Pros_ID respectively, to see what training they got or what procedures they read)</li>
  </ul>
  
  <p>I want to select a table that simply shows the "Sicil" of the employee and "Proj_ID" of the projects that they can work in. Which means they have at least took the trainings and read the procedures that is the prerequisite of a project. If they did more than that, it's OK. I want to do this for the entire database and find every employee's project that they are autorised and can possibly work on.</p>
  
  <p>I have gotten this far in my SQL Query but it is not accurate. I am guessing that it has got something to do with there being so many join operations:</p>
  
  <pre><code>    SELECT DISTINCT emp.Sicil, proj.Proj_ID
  FROM tbl_yet_Proje proj FULL OUTER JOIN tbl_yet_ProjeEgitim pe ON proj.Proj_ID = pe.Proj_ID
  INNER JOIN tbl_yet_ProjeProsedur pp on proj.Proj_ID = pp.Proj_ID 
  INNER JOIN tbl_yet_EmployeeEgitim ee ON pe.TR_ID = ee.TR_ID
  INNER JOIN tbl_yet_EmployeeProsedur ep ON ep.Pros_ID = pp.Pros_ID
  INNER JOIN vw_Employee emp ON emp.Sicil = ep.Sicil AND emp.Sicil = ee.Sicil
  </code></pre>
  
  <p>Can anyone help me with this?
  Thank you so much</p>