Not able to produce table based on conditional select
You're probably looking at needing an OUTER JOIN instead of the implied INNER JOINs that you're using. e.g. suppose 

   Table 1 Employee (Empl_number, Empl_Name)
   001, Paul
   002, John
   003, Fred

   Table 2 EmployeeRoles (Role_ID, Role_Desc)
   100, Manager
   200, Clerk
   300, KeyHolder

   Table 3 RoleLink (ID, Empl_number, Role_ID
   1, 001, 100
   2, 001, 300
   3, 002, 200


Then to get a list of all employees and their roles you would use

   SELECT e.empl_number, e.empl_name, ISNULL(r.role_desc, 'No role')
   FROM EMPLOYEE e
   LEFT OUTER JOIN ROLELINK l on e.empl_number = l.empl_number
   LEFT OUTER JOIN EMPLOYEEROLES r on l.role_id = r.role_id 


Would give

   001, Paul, Manager
   001, Paul, Keyholder
   002, John, No role
   003, Fred, Clerk