This exception is almost killing me today !!
an inner member of an outer-join clause. This is not allowed if the table also participates in a regular join clause.
after try and tests and resolves, wiki helps me..
Alternatives The effect of an outer join can also be obtained using a UNION ALL between an INNER JOIN and a SELECT of the rows in the "main" table that do not fulfill the join condition. For example SELECT employee.LastName, employee.DepartmentID, department.DepartmentName FROM employee LEFT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID; can also be written as SELECT employee.LastName, employee.DepartmentID, department.DepartmentName FROM employee INNER JOIN department ON employee.DepartmentID = department.DepartmentID UNION ALL SELECT employee.LastName, employee.DepartmentID, CAST(NULL AS VARCHAR(20)) FROM employee WHERE NOT EXISTS ( SELECT * FROM department WHERE employee.DepartmentID = department.DepartmentID)
looks easy, turning back, maybe i should have became calmer and more relax, which should be all the case !!
so simply, overall, use less outer join !!!!! when must there is outer join, cannot mix regular join there.