Q:

SQL join to return results only if all rows match

this is my first time on the site so please be gentle.  I have tried to find my answer here, but nothing seems to fit the bill and after reading up on different types of joins, I'm still none the wiser. 
I have the following tables:
employees (employee_id, name)
assessments (assessment_id, name, pass_score)
authorizations (authorization_id, name)

Employees take assessments which is logged in:
assessments_taken (assessment_taken_id, employee_id, assessment_id, score)

An authorization is composed of multiple individual assessments given by:
authorization_requirements (auth_req_id, authorization_id, assessment_id)

where authorization_id & assessment_id are unique constraints to prevent duplicates of the pair. 
The query I am trying to design is something along the lines of:
SELECT employees.name, authorization.name ...

I only want to return  employees and authorizations where an employee has passed all assessments for that authorization given in authorization_requirements. 
Can anyone help me find the query/sub-queries I am looking for?  

A:

The below image may help you somewhat in learning SQL joins - it even gives you some of the SQL itself!.  As for the authorization_requirements it sounds like you'll need to be using some WHERE clause along with the join. 
Hope this helps, and any clarification, please feel free to ask! 

A:

Have a look at this http://sqlfiddle.com/#!2/ac7887/2
I think this query will do it for you.  It relies on there only being one entry in the Assessments Taken per employee per assessment.  If employees can take the assessments multiple times and all results are recorded in this table, there's a second query that should work for you. 
SELECT
  Employees.name Employee,
  authorizations.name Authorization
FROM
  employees
  CROSS JOIN authorizations
  INNER JOIN authorization_requirements ON authorizations.authorization_id = authorization_requirements.authorization_id
  INNER JOIN assessments ON authorization_requirements.assessment_id = assessments.assessment_id
  LEFT JOIN assessments_taken ON employees.employee_id = assessments_taken.employee_id AND assessments.assessment_id = assessments_taken.assessment_id AND assessments.pass_score <= assessments_taken.score
GROUP BY
  Employees.name,
  authorizations.name
HAVING
  COUNT(assessments.assessment_id) = COUNT(assessments_taken.assessment_taken_id)

If multiple entries in assessments_taken:
SELECT
  Employees.name Employee,
  authorizations.name Authorization
FROM
  employees
  CROSS JOIN authorizations
  INNER JOIN authorization_requirements ON authorizations.authorization_id = authorization_requirements.authorization_id
  INNER JOIN assessments ON authorization_requirements.assessment_id = assessments.assessment_id
  LEFT JOIN (SELECT
               assessments_taken.employee_id,
               assessments_taken.assessment_id,
               MAX(Score) best_score
             FROM
               assessments_taken
             GROUP BY
               assessments_taken.employee_id,
               assessments_taken.assessment_id
             ) best_assessments_taken ON employees.employee_id = best_assessments_taken.employee_id AND assessments.assessment_id = best_assessments_taken.assessment_id AND assessments.pass_score <= best_assessments_taken.best_score
GROUP BY
  Employees.name,
  authorizations.name
HAVING
  COUNT(assessments.assessment_id) = COUNT(best_assessments_taken.assessment_id)

These queries get every employee and authorization and then ensure that the employee has got an entry in assessments_taken which exceeds the required score for every assessment specified in authorization_requirements
If CROSS JOIN is not supported you might need to put those two tables in a sub query and then change the join conditions to reference the columns from the sub query instead:
SELECT
  employee_authorizations.employee_name Employee,
  employee_authorizations.authorization_name Authorization
FROM
  (SELECT
        employees.employee_id,
        employees.name employee_name,
        authorizations.authorization_id,
        authorizations.name authorization_name
  FROM
        employees,
        authorizations) employee_authorizations
  INNER JOIN authorization_requirements ON employee_authorizations.authorization_id = authorization_requirements.authorization_id
  INNER JOIN assessments ON authorization_requirements.assessment_id = assessments.assessment_id
  LEFT JOIN (SELECT
               assessments_taken.employee_id,
               assessments_taken.assessment_id,
               MAX(Score) best_score
             FROM
               assessments_taken
             GROUP BY
               assessments_taken.employee_id,
               assessments_taken.assessment_id
             ) best_assessments_taken ON employee_authorizations.employee_id = best_assessments_taken.employee_id AND assessments.assessment_id = best_assessments_taken.assessment_id AND assessments.pass_score <= best_assessments_taken.best_score
GROUP BY
  employee_authorizations.employee_name,
  employee_authorizations.authorization_name
HAVING
  COUNT(assessments.assessment_id) = COUNT(best_assessments_taken.assessment_id)

