SQL relation between 3 tables

Question

I need some help with an SQL statement...

I have tree tables

• Problems
• Solutions
• Employees

The Problem table contains an ID, a description of the problem and the ID of the person who reported it.

The Solution table contains an ID, description of the solution and the ID of the person who solved the problem.

The Employee table contains an ID, the first and last names of all the employees of a company.

The ID of the solver and the reporter are related to the ID of the employee

I want to write a query that gives me the description of the problem and the solution and the first and last name of the employee who reported and solved it.

This is the query I have to select everything I need except the name of the solver..

SELECT        
   P.ProblemID, 
   P.Description AS ProblemDescription, 
   P.SolutionID,  
   P.ReporterID, 
   E.FirstName,
   E.LastName, 
   S.SolverID, 
   S.Description AS SolutionDescription
FROM            
   (tblProblem P 
LEFT OUTER JOIN 
   tblEmployee E ON P.ReporterID = E.EmployeeID) 
LEFT OUTER JOIN 
   tblSolution S ON P.SolutionID = S.SolutionID;

I really hope someone can help me with this..

Thanks in advance!

Answer 1

Just join the employee table again, once for the solver and once for the reporter:

SELECT        P.ProblemID, 
              P.Description AS ProblemDescription, 
              P.SolutionID,

              P.ReporterID, 
              RE.FirstName as ReporterFirstname,
              RE.LastName as ReporterLastname, 
              S.SolverID, 
              S.Description AS SolutionDescription,
              SE.FirstName as SolverFirstname,
              SE.LastName as SolverLastname
FROM

             ((tblProblem P LEFT OUTER JOIN 
              tblEmployee RE ON P.ReporterID = RE.EmployeeID) LEFT OUTER JOIN 
              tblSolution S ON P.SolutionID = S.SolutionID) LEFT OUTER JOIN 
              tblEmployee SE ON P.ReporterID = SE.EmployeeID

PS: I saw you using parentheses. Are you using Access? If so, I hope I got them right.

Answer 2

You need to join on the employee marked as the solver as well. Following your style and not knowing your exact column names:

SELECT        
          P.ProblemID, 
          P.Description AS ProblemDescription, 
          P.SolutionID,

          P.ReporterID, 
          E.FirstName,
          E.LastName, 
          S.SolverID, 
          S.Description AS SolutionDescription,
          E2.FirstName as SolverFirstName,
          E2.LastName as SolverLastName

FROM

         (tblProblem P LEFT OUTER JOIN 
          tblEmployee E ON P.ReporterID = E.EmployeeID) 
          LEFT OUTER JOIN 
          tblSolution S ON P.SolutionID = S.SolutionID
          LEFT OUTER JOIN
          tblEmployee E2 ON S.SolverID = E2.EmployeeID;