CODEWITHSUNDEEP
sqlms-access
Daniel
Daniel

Reputation: 21

SQL with left outer join and 3 tables

I would like to add an ADD at the end of my code. Please have a look on my code and thanks for your support:

SELECT Area.org, 
   Supervisors.NomSup, 
   Supervisors.PrenomSup, 
   Employees.NomEmp, 
   Employees.PrenomEmp, 
   Employees.NoIdAlcanEmp, 
   Competencies.CodeCompetencies, 
   Competencies.CompetencyName, 
   LinkResultComp.AssNote, 
   LinkResultComp.AssDate
FROM ((((((
       Area INNER JOIN Supervisors ON Area.IdArea = Supervisors.IdArea
      )
      INNER JOIN Employees ON Supervisors.IdSupervisor = Employees.IdSupervisor
     )
     INNER JOIN LinkProfilesEmployees ON Employees.IdEmp = LinkProfilesEmployees.IdEmp
    )
    INNER JOIN Profiles ON Profiles.IdProfiles = LinkProfilesEmployees.IdProfiles
   )
   INNER JOIN LinkProfComp ON Profiles.IdProfiles = LinkProfComp.IdProfiles
  )
  INNER JOIN Competencies ON Competencies.IdCompetencies = LinkProfComp.IdCompetencies
 )
 LEFT OUTER JOIN LinkResultComp ON (Competencies.IdCompetencies = LinkResultComp.IdCompetencies AND ON Competencies.IdCompetencies = LinkResultComp.IdCompetencies)

WHERE Area.org LIKE "*20*" AND Competencies.CodeCompetencies LIKE "khse2010-05"
ORDER BY Supervisors.NomSup, Employees.NomEmp;

Upvotes: 1

Views: 1720

Answers (1)

Conrad Frix
Conrad Frix

Reputation: 52675

Just remove the extra ON that you added

So change this

LEFT OUTER JOIN LinkResultComp 
ON (Competencies.IdCompetencies = LinkResultComp.IdCompetencies 
  AND ON Competencies.IdCompetencies = LinkResultComp.IdCompetencies)
------^^ This one

to this

LEFT OUTER JOIN LinkResultComp 
ON (Competencies.IdCompetencies = LinkResultComp.IdCompetencies 
  AND Competencies.IdCompetencies = LinkResultComp.IdCompetencies)

Of course I assume you meant different fields for the second condition

Upvotes: 2

Related Questions